Katkat
Back

Calculate the Median House Price in Each City

AirbnbData Engineer · Onsite
MediumData EngineerOnsiteData Manipulation (SQL/Python)

Last updated

Tests your ability to compute grouped medians in PostgreSQL, properly handling even and odd row counts, duplicate price values, and numeric precision.

Problem Statement

As a data engineer at Airbnb, you are tasked with analyzing city-level housing affordability. Write a PostgreSQL query to compute the median listing price for each city.

The median is defined as:

  • For an odd number of listings in a city, the middle price when sorted in ascending order.
  • For an even number of listings, the arithmetic mean of the two middle prices.

Your query should return the city name and the median price rounded to two decimal places, sorted alphabetically by city name.

Input Schema

Table: listings

+-------------+---------------+ 
| Column Name | Type          | 
+-------------+---------------+ 
| id          | INT           | 
| city        | VARCHAR(100)  | 
| price       | NUMERIC(10,2) | 
+-------------+---------------+
  • id is the primary key.
  • price is positive and non-null.
  • city is non-null.

Output Schema

+--------------+---------------+ 
| Column Name  | Type          | 
+--------------+---------------+ 
| city         | VARCHAR(100)  | 
| median_price | NUMERIC(10,2) | 
+--------------+---------------+

Example

Input

listings table:

| id | city          | price  |
|----|---------------|--------|
| 1  | Austin        | 100.00 |
| 2  | Austin        | 200.00 |
| 3  | Austin        | 300.00 |
| 4  | Austin        | 400.00 |
| 5  | San Francisco | 150.00 |
| 6  | San Francisco | 250.00 |
| 7  | San Francisco | 350.00 |
| 8  | Seattle       | 500.00 |

Output

| city          | median_price |
|---------------|--------------|
| Austin        | 250.00       |
| San Francisco | 250.00       |
| Seattle       | 500.00       |

Explanation

  • Austin: 4 listings (even count). The two middle prices are 200.00 and 300.00. The average is (200.00 + 300.00) / 2 = 250.00.
  • San Francisco: 3 listings (odd count). The middle price is 250.00.
  • Seattle: 1 listing (odd count). The middle price is 500.00.

Constraints & Edge Cases

  • Each city will have at least 1 listing.
  • Handle duplicate prices properly; do not deduplicate prices before finding the median.
  • Group sizes can be arbitrarily large (from 1 to millions of rows).

Hints

Hint 1

PostgreSQL supports ordered-set aggregate functions such as PERCENTILE_CONT that directly compute continuous percentiles.

Hint 2

If implementing with window functions instead, use ROW_NUMBER() over (PARTITION BY city ORDER BY price) to assign deterministic ranks to every row including ties.

Hint 3

For the window function approach, filter for rows where the rank is within [FLOOR((N + 1) / 2.0), CEIL((N + 1) / 2.0)], then compute the average.

Follow-up questions

  • What is the difference between PERCENTILE_CONT and PERCENTILE_DISC, and which one would produce an actual existing listing price in even-sized groups?
  • How would you optimize this query if the listings table contained 500 million rows partitioned by country?
  • How would you implement this logic in Python/Pandas, and what memory considerations would arise with large datasets?