Rate per Square Foot by Locality — SQL Aggregation Problem
A real estate research desk publishes a locality's rate per square foot as the total asking price of its active listings divided by their total carpet area…
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #349
Problem statement
A real estate research desk publishes a locality's rate per square foot as the total asking price of its active listings divided by their total carpet area — a weighted rate, so a big flat counts for more than a small one.
Return one row per locality that has at least one active listing, with locality, listings (the number of active listings) and rate_per_sqft rounded to the nearest rupee. Order the rows by rate_per_sqft descending, ties by locality ascending. Sold and withdrawn listings are ignored.
Tables
Table: PropertyListing
| Column | Type |
|---|---|
| listing_id | int |
| locality | varchar |
| carpet_sqft | int |
| asking_price | int |
| status | enum(active, sold, withdrawn) |
Primary key: listing_id.
asking_price is in rupees; carpet_sqft is the flat's carpet area in square feet.
Examples
Example 1
PropertyListing
| listing_id | locality | carpet_sqft | asking_price | status |
|---|---|---|---|---|
| 1 | Whitefield | 1150 | 9200000 | active |
| 2 | Whitefield | 1600 | 13800000 | active |
| 3 | HSR Layout | 1200 | 14400000 | active |
| 4 | HSR Layout | 900 | 12500000 | sold |
| 5 | Hebbal | 1400 | 12600000 | active |
| 6 | Electronic City | 1050 | 5600000 | active |
| 7 | Electronic City | 980 | 5100000 | withdrawn |
| 8 | Koramangala | 1000 | 12000000 | active |
Output
| locality | listings | rate_per_sqft |
|---|---|---|
| HSR Layout | 1 | 12000 |
| Koramangala | 1 | 12000 |
| Hebbal | 1 | 9000 |
| Whitefield | 2 | 8364 |
| Electronic City | 1 | 5333 |
How to solve Rate per Square Foot by Locality
Filter first: only active listings belong in the published rate, so WHERE status = 'active' runs before the grouping, and a locality whose listings are all sold or withdrawn disappears from the answer altogether — which is what the statement asks.
Then GROUP BY locality and compute two sums per group: the total asking price and the total carpet area. Their ratio is the weighted rate. It is not the same as AVG(asking_price / carpet_sqft) — the average of per-flat rates gives a 450 sq ft studio the same say as a 2,000 sq ft penthouse, which is not how the desk defines it.
Round the ratio once at the end; rounding the inputs would drift. The order is by the rounded rate, highest first, with the locality name as the tie-breaker. Conditional aggregation over the whole table, with a HAVING that drops localities without active listings, is an equivalent route. One pass over the table either way.
Reference solution (MySQL)
SELECT locality,
COUNT(*) AS listings,
ROUND(SUM(asking_price) / SUM(carpet_sqft)) AS rate_per_sqft
FROM PropertyListing
WHERE status = 'active'
GROUP BY locality
ORDER BY rate_per_sqft DESC, localityAnother way
SELECT locality, COUNT(listing_id) AS listings, ROUND(SUM(asking_price) * 1.0 / SUM(carpet_sqft), 0) AS rate_per_sqft FROM PropertyListing WHERE status IN ('active') GROUP BY locality ORDER BY 3 DESC, 1Another way
SELECT locality, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS listings, ROUND(SUM(CASE WHEN status = 'active' THEN asking_price END) / SUM(CASE WHEN status = 'active' THEN carpet_sqft END)) AS rate_per_sqft FROM PropertyListing GROUP BY locality HAVING SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) > 0 ORDER BY rate_per_sqft DESC, locality← Ready-to-Move 2 BHK Listings Under ₹80 Lakh · Leads Never Taken on a Completed Site Visit →