Median Registered Rate per Sq Ft by Locality — SQL Problem
A housing-price index uses the median registered rate per square foot rather than the mean, so one luxury sale cannot skew a locality.
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #367
Problem statement
A housing-price index uses the median registered rate per square foot rather than the mean, so one luxury sale cannot skew a locality. Use only transactions registered in 2024, and only localities with at least 3 such transactions.
The median of an odd number of rates is the middle one; of an even number, the average of the two middle ones. Return locality, transactions and median_rate rounded to 1 decimal, ordered by median_rate descending, then locality.
Tables
Table: SaleRegistration
| Column | Type |
|---|---|
| txn_id | int |
| locality | varchar |
| registered_on | date |
| rate_per_sqft | int |
Primary key: txn_id.
One row per sale deed registered; rate_per_sqft is the consideration divided by the carpet area, in rupees.
Examples
Example 1
SaleRegistration
| txn_id | locality | registered_on | rate_per_sqft |
|---|---|---|---|
| 1 | Whitefield | 2024-02-11 | 7800 |
| 2 | Whitefield | 2024-05-02 | 9100 |
| 3 | Whitefield | 2024-07-19 | 8200 |
| 4 | Whitefield | 2024-09-30 | 21000 |
| 5 | Hebbal | 2024-03-14 | 11200 |
| 6 | Hebbal | 2024-06-21 | 10400 |
| 7 | Hebbal | 2024-11-05 | 10900 |
| 8 | Hebbal | 2023-12-28 | 9800 |
| 9 | Koramangala | 2024-04-17 | 16500 |
| 10 | Koramangala | 2025-01-03 | 17200 |
Output
| locality | transactions | median_rate |
|---|---|---|
| Hebbal | 3 | 10900 |
| Whitefield | 4 | 8650 |
How to solve Median Registered Rate per Sq Ft by Locality
SQL has no portable MEDIAN, so build it from positions. Within each locality's 2024 transactions, ROW_NUMBER() OVER (PARTITION BY locality ORDER BY rate_per_sqft, txn_id) gives every rate its rank in sorted order, and COUNT(*) OVER (PARTITION BY locality) gives n on every row. The middle positions are FLOOR((n + 1) / 2) and FLOOR((n + 2) / 2): for n = 3 both are 2, for n = 4 they are 2 and 3. Keeping those rows and averaging them per locality gives the median in both cases.
The year filter runs before the windows so that n counts 2024 deeds only — Hebbal's December 2023 deed and Koramangala's January 2025 one do not count, which drops Koramangala below the 3-transaction floor. Whitefield's ₹21,000 luxury sale barely moves its median (8,650), which is the point of using one. Equal rates are harmless: the tie-break by txn_id fixes the numbering, and equal values give the same median whichever comes first.
Without windows, a correlated count of the rates at or below each row (ties broken by id) gives the same position.
Reference solution (MySQL)
WITH y AS (
SELECT locality, rate_per_sqft,
ROW_NUMBER() OVER (PARTITION BY locality ORDER BY rate_per_sqft, txn_id) AS rn,
COUNT(*) OVER (PARTITION BY locality) AS cnt
FROM SaleRegistration
WHERE registered_on BETWEEN '2024-01-01' AND '2024-12-31'
)
SELECT locality, MAX(cnt) AS transactions, ROUND(AVG(rate_per_sqft), 1) AS median_rate
FROM y
WHERE cnt >= 3
AND rn IN (FLOOR((cnt + 1) / 2), FLOOR((cnt + 2) / 2))
GROUP BY locality
ORDER BY median_rate DESC, localityAnother way
WITH y AS (SELECT locality, rate_per_sqft, ROW_NUMBER() OVER (PARTITION BY locality ORDER BY rate_per_sqft, txn_id) AS up, ROW_NUMBER() OVER (PARTITION BY locality ORDER BY rate_per_sqft DESC, txn_id DESC) AS down FROM SaleRegistration WHERE YEAR(registered_on) = 2024) SELECT locality, MAX(up + down - 1) AS transactions, ROUND(AVG(rate_per_sqft), 1) AS median_rate FROM y WHERE ABS(CAST(up AS SIGNED) - CAST(down AS SIGNED)) <= 1 GROUP BY locality HAVING MAX(up + down - 1) >= 3 ORDER BY median_rate DESC, localityAnother way
WITH y AS (SELECT txn_id, locality, rate_per_sqft FROM SaleRegistration WHERE YEAR(registered_on) = 2024), r AS (SELECT a.locality, a.rate_per_sqft, (SELECT COUNT(*) FROM y b WHERE b.locality = a.locality AND (b.rate_per_sqft < a.rate_per_sqft OR (b.rate_per_sqft = a.rate_per_sqft AND b.txn_id <= a.txn_id))) AS rn, (SELECT COUNT(*) FROM y c WHERE c.locality = a.locality) AS cnt FROM y a) SELECT locality, MAX(cnt) AS transactions, ROUND(AVG(rate_per_sqft), 1) AS median_rate FROM r WHERE cnt >= 3 AND (rn * 2 = cnt + 1 OR rn * 2 = cnt OR rn * 2 = cnt + 2) GROUP BY locality ORDER BY median_rate DESC, locality← Tenants Late on Rent Three Months Running · Enquiry-to-Token Funnel by Lead Source →