Calculate the Median House Price in Each City

Compute the median house price for each city using PostgreSQL. A city may have an odd or even number of price observations.

Input Table

house_prices

Column	Type	Meaning
observation_id	INTEGER	Unique identifier for one observation; primary key
city	TEXT	Non-null city name
price	NUMERIC(14,2)	Non-null, nonnegative price

The table is the relational representation of city-associated price records for this exercise. Every row is one observation. Repeated prices remain separate observations, even within the same city.

Output Contract

Write one read-only PostgreSQL query returning city and median_price, with one row per city present in house_prices. Sort the output by city ascending. For an odd count, use the middle sorted price. For an even count, use the arithmetic mean of the two middle prices. Return the exact numeric median without rounding to a whole unit.

Example

observation_id	city	price
1	Alpha	100.00
2	Alpha	200.00
3	Alpha	400.00
4	Beta	200.00
5	Beta	300.00

Expected result:

city	median_price
Alpha	200.00
Beta	250.00

Constraints and Clarifications

• City names use English letters and are compared as stored; do not merge differently spelled names.
• A city with one observation has that observation's price as its median.
• Duplicate prices affect the middle positions; do not apply DISTINCT to prices.
• There is no separate city catalog, so cities without observations are absent.
• The median may require three decimal places when the two central prices differ by one cent.

Hint: Identify the central positions

The middle of a city's sorted observations depends on its count. Consider how the same selection of central positions can handle both odd and even counts.

Median House Price by City

Table house_prices

column	type	constraints
observation_id	INTEGER	PRIMARY KEY
city	TEXT	NOT NULL
price	NUMERIC(14,2)	NOT NULL

Sample data:

city	observation_id	price
Denver	1	390000
Austin	2	310000
Boston	3	610000
Pittsburgh	4	215000
Austin	5	450000
Chicago	6	265000
Boston	7	575000.5
Austin	8	310000
Denver	9	389999.99
Pittsburg	10	98000
Boston	11	702000
Austin	12	525000
Pittsburgh	13	189500
Boston	14	575000.49
Austin	15	298000
Pittsburgh	16	240250

Expected output

city	median_price
Austin	310000
Boston	592500.25
Chicago	265000
Denver	389999.995
Pittsburg	98000
Pittsburgh	215000

Hints

1. The middle of a city's sorted observations depends on its count. Consider how the same selection of central positions can handle both odd and even counts.