Median Registered Rate per Sq Ft by Locality

Hard
367.

Median Registered Rate per Sq Ft by Locality

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

ColumnType
txn_idint
localityvarchar
registered_ondate
rate_per_sqftint

Primary key: txn_id.

One row per sale deed registered; rate_per_sqft is the consideration divided by the carpet area, in rupees.

Example

The rows must come back in the order shown.

SaleRegistration

txn_idlocalityregistered_onrate_per_sqft
1Whitefield2024-02-117800
2Whitefield2024-05-029100
3Whitefield2024-07-198200
4Whitefield2024-09-3021000
5Hebbal2024-03-1411200
6Hebbal2024-06-2110400
7Hebbal2024-11-0510900
8Hebbal2023-12-289800
9Koramangala2024-04-1716500
10Koramangala2025-01-0317200

Output

localitytransactionsmedian_rate
Hebbal310900
Whitefield48650
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
txn_idint
localityvarchar
registered_ondate
rate_per_sqftint

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_idlocalityregistered_onrate_per_sqft
1Whitefield2024-02-117800
2Whitefield2024-05-029100
3Whitefield2024-07-198200
4Whitefield2024-09-3021000
5Hebbal2024-03-1411200
6Hebbal2024-06-2110400
7Hebbal2024-11-0510900
8Hebbal2023-12-289800
9Koramangala2024-04-1716500
10Koramangala2025-01-0317200

Output

localitytransactionsmedian_rate
Hebbal310900
Whitefield48650

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, locality

Another 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, locality

Another 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 →