Calculate the Median House Price in Each City
Company: Airbnb
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
```
Overview: Calculate median house prices by city in PostgreSQL, handling odd and even group sizes, repeated prices, and exact numeric results.
Read the full Airbnb Data Engineer interview experience this question came from
Compute the median house price for each city using PostgreSQL. A city may have an odd or even number of price observations.
Table `house_prices` has columns `observation_id` (INTEGER, primary key, one row per price observation), `city` (TEXT, non-null city name) and `price` (NUMERIC(14,2), non-null, nonnegative). Every row is one observation; repeated prices remain separate observations, even within the same city.
Write one read-only PostgreSQL query returning `city` and `median_price`, with one row per city present in `house_prices`, sorted 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.
Clarifications: city names are compared as stored, so differently spelled names are not merged. A city with one observation has that observation's price as its median. Duplicate prices affect the middle positions, so 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.
Tables
house_prices(observation_id INTEGER, city TEXT, price NUMERIC(14,2))
Hints
- 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.