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
| Column | Type |
|---|---|
| locality_id | int |
| locality_name | varchar |
| city | varchar |
Primary key: locality_id.
Table: RentalListing
| Column | Type |
|---|---|
| listing_id | int |
| locality_id | int |
| bhk | int |
| monthly_rent | int |
Primary key: listing_id.
locality_id is always a locality in Locality. monthly_rent is in rupees.
Examples
Example 1
Locality
| locality_id | locality_name | city |
|---|---|---|
| 1 | Baner | Pune |
| 2 | Kharadi | Pune |
| 3 | Whitefield | Bengaluru |
| 4 | Koramangala | Bengaluru |
| 5 | Gachibowli | Hyderabad |
RentalListing
| listing_id | locality_id | bhk | monthly_rent |
|---|---|---|---|
| 10 | 1 | 2 | 32000 |
| 11 | 2 | 3 | 45000 |
| 12 | 1 | 3 | 45000 |
| 13 | 3 | 2 | 38000 |
| 14 | 4 | 3 | 72000 |
| 15 | 4 | 2 | 41000 |
| 16 | 2 | 1 | 18000 |
Output
| city | locality_name | listing_id | monthly_rent |
|---|---|---|---|
| Bengaluru | Koramangala | 14 | 72000 |
| Pune | Kharadi | 11 | 45000 |
| Pune | Baner | 12 | 45000 |
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 = 1Another 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 →