Costliest Rental Listing in Each City

Medium
356.

Costliest Rental Listing in Each City

A rental portal's homepage features the highest monthly rent asked in each city. Each listing sits in a locality, and each locality belongs to a city. When several listings in a city share the highest rent, feature all of them.

Return city, locality_name, listing_id and monthly_rent for the featured listings, in any order. Cities without listings do not appear.

Tables

Table: Locality

ColumnType
locality_idint
locality_namevarchar
cityvarchar

Primary key: locality_id.

Table: RentalListing

ColumnType
listing_idint
locality_idint
bhkint
monthly_rentint

Primary key: listing_id.

locality_id is always a locality in Locality. monthly_rent is in rupees.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Locality

locality_idlocality_namecity
1BanerPune
2KharadiPune
3WhitefieldBengaluru
4KoramangalaBengaluru
5GachibowliHyderabad

RentalListing

listing_idlocality_idbhkmonthly_rent
101232000
112345000
121345000
133238000
144372000
154241000
162118000

Output

citylocality_namelisting_idmonthly_rent
BengaluruKoramangala1472000
PuneKharadi1145000
PuneBaner1245000
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.

Costliest Rental Listing in Each City — SQL Problem

A rental portal's homepage features the highest monthly rent asked in each city. Each listing sits in a locality, and each locality belongs to a city.

  • Difficulty: Medium
  • Topics: Window Functions, Joins
  • Dialect: MySQL
  • Problem: #356

Problem statement

A rental portal's homepage features the highest monthly rent asked in each city. Each listing sits in a locality, and each locality belongs to a city. When several listings in a city share the highest rent, feature all of them.

Return city, locality_name, listing_id and monthly_rent for the featured listings, in any order. Cities without listings do not appear.

Tables

Table: Locality

ColumnType
locality_idint
locality_namevarchar
cityvarchar

Primary key: locality_id.

Table: RentalListing

ColumnType
listing_idint
locality_idint
bhkint
monthly_rentint

Primary key: listing_id.

locality_id is always a locality in Locality. monthly_rent is in rupees.

Examples

Example 1

Locality

locality_idlocality_namecity
1BanerPune
2KharadiPune
3WhitefieldBengaluru
4KoramangalaBengaluru
5GachibowliHyderabad

RentalListing

listing_idlocality_idbhkmonthly_rent
101232000
112345000
121345000
133238000
144372000
154241000
162118000

Output

citylocality_namelisting_idmonthly_rent
BengaluruKoramangala1472000
PuneKharadi1145000
PuneBaner1245000

How to solve Costliest Rental Listing in Each City

Two steps: attach the city to each listing, then keep each city's top rent. The join RentalListing → Locality gives every listing its city and locality name. Then RANK() OVER (PARTITION BY city ORDER BY monthly_rent DESC) numbers listings within each city from the most expensive down, and the outer query keeps rank 1.

Ties are why RANK is the right function: in Pune, Baner and Kharadi both have a 3 BHK at ₹45,000, both get rank 1, and both are featured. ROW_NUMBER would pick one arbitrarily, and the answer would depend on storage order. DENSE_RANK would also work for rank 1.

The classic alternative computes each city's MAX(monthly_rent) — in a correlated subquery or a grouped CTE — and joins back to keep listings equal to it; it handles ties the same way. Note the partition is the city even though the output shows the locality: grouping by locality would feature one listing per locality instead.

Reference solution (MySQL)

SELECT city, locality_name, listing_id, monthly_rent
FROM (
  SELECT l.city, l.locality_name, r.listing_id, r.monthly_rent,
         RANK() OVER (PARTITION BY l.city ORDER BY r.monthly_rent DESC) AS rnk
  FROM RentalListing r
  JOIN Locality l ON l.locality_id = r.locality_id
) ranked
WHERE rnk = 1

Another way

SELECT l.city, l.locality_name, r.listing_id, r.monthly_rent FROM RentalListing r JOIN Locality l ON l.locality_id = r.locality_id WHERE r.monthly_rent = (SELECT MAX(r2.monthly_rent) FROM RentalListing r2 JOIN Locality l2 ON l2.locality_id = r2.locality_id WHERE l2.city = l.city)

Another way

WITH tops AS (SELECT l.city, MAX(r.monthly_rent) AS top_rent FROM RentalListing r JOIN Locality l ON l.locality_id = r.locality_id GROUP BY l.city) SELECT l.city, l.locality_name, r.listing_id, r.monthly_rent FROM RentalListing r JOIN Locality l ON l.locality_id = r.locality_id JOIN tops t ON t.city = l.city AND t.top_rent = r.monthly_rent

← Leases Ending Within the Next 30 Days · Broker Site-Visit Conversion Rate →