Rate per Square Foot by Locality

Easy
349.

Rate per Square Foot by Locality

EasyAggregation

MySQL

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

ColumnTypeValues
listing_idint
localityvarchar
carpet_sqftint
asking_priceint
statusenumactive, sold, withdrawn

Primary key: listing_id.

asking_price is in rupees; carpet_sqft is the flat's carpet area in square feet.

Example

The rows must come back in the order shown.

PropertyListing

listing_idlocalitycarpet_sqftasking_pricestatus
1Whitefield11509200000active
2Whitefield160013800000active
3HSR Layout120014400000active
4HSR Layout90012500000sold
5Hebbal140012600000active
6Electronic City10505600000active
7Electronic City9805100000withdrawn
8Koramangala100012000000active

Output

localitylistingsrate_per_sqft
HSR Layout112000
Koramangala112000
Hebbal19000
Whitefield28364
Electronic City15333
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.

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

ColumnType
listing_idint
localityvarchar
carpet_sqftint
asking_priceint
statusenum(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_idlocalitycarpet_sqftasking_pricestatus
1Whitefield11509200000active
2Whitefield160013800000active
3HSR Layout120014400000active
4HSR Layout90012500000sold
5Hebbal140012600000active
6Electronic City10505600000active
7Electronic City9805100000withdrawn
8Koramangala100012000000active

Output

localitylistingsrate_per_sqft
HSR Layout112000
Koramangala112000
Hebbal19000
Whitefield28364
Electronic City15333

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

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

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