Ready-to-Move 2 BHK Listings Under ₹80 Lakh — SQL Problem
A property portal is sending a shortlist to a buyer who wants a 2 BHK that is ready to move in, still active on the site, with an asking price of at most…
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #348
Problem statement
A property portal is sending a shortlist to a buyer who wants a 2 BHK that is ready to move in, still active on the site, with an asking price of at most ₹80 lakh.
Return listing_id, locality and price_lakh of every listing that meets all four conditions, ordered by price_lakh ascending, ties by listing_id ascending. A listing priced exactly 80 lakh qualifies.
Tables
Table: Listing
| Column | Type |
|---|---|
| listing_id | int |
| locality | varchar |
| bhk | int |
| price_lakh | int |
| possession | enum(ready, under_construction) |
| status | enum(active, sold, withdrawn) |
Primary key: listing_id.
One row per flat listed for sale. price_lakh is the asking price in lakh rupees (1 lakh = ₹1,00,000).
Examples
Example 1
Listing
| listing_id | locality | bhk | price_lakh | possession | status |
|---|---|---|---|---|---|
| 201 | Baner | 2 | 78 | ready | active |
| 202 | Wakad | 2 | 64 | ready | active |
| 203 | Wakad | 3 | 72 | ready | active |
| 204 | Kharadi | 2 | 80 | ready | active |
| 205 | Hinjewadi | 2 | 58 | under_construction | active |
| 206 | Aundh | 2 | 79 | ready | sold |
| 207 | Kothrud | 2 | 81 | ready | active |
| 208 | Hadapsar | 2 | 64 | ready | active |
Output
| listing_id | locality | price_lakh |
|---|---|---|
| 202 | Wakad | 64 |
| 208 | Hadapsar | 64 |
| 201 | Baner | 78 |
| 204 | Kharadi | 80 |
How to solve Ready-to-Move 2 BHK Listings Under ₹80 Lakh
Each requirement — the configuration, the possession status, the listing status and the budget — is a test on one row, so the whole question is one WHERE clause with the four conditions joined by AND. A row survives only if all four hold.
The budget is inclusive: "at most ₹80 lakh" means price_lakh <= 80, so the flat listed at exactly 80 lakh stays, and the one at 81 does not. The sold flat in Aundh and the under-construction one in Hinjewadi fail on status and possession even though their prices fit.
The order is fixed by the statement: price first, and because two flats can be listed at the same price, listing_id second so the result is the same however the rows are stored. Equivalent spellings — NOT (price_lakh > 80), IN lists of one value, a derived table that filters status first — return the same rows. The query reads the table once; an index on (status, bhk, price_lakh) would let a large portal answer it without a full scan.
Reference solution (MySQL)
SELECT listing_id, locality, price_lakh
FROM Listing
WHERE bhk = 2
AND possession = 'ready'
AND status = 'active'
AND price_lakh <= 80
ORDER BY price_lakh, listing_idAnother way
SELECT listing_id, locality, price_lakh FROM Listing WHERE NOT (price_lakh > 80) AND bhk BETWEEN 2 AND 2 AND possession IN ('ready') AND status IN ('active') ORDER BY 3, 1Another way
SELECT listing_id, locality, price_lakh FROM (SELECT * FROM Listing WHERE status = 'active' AND possession = 'ready') a WHERE bhk = 2 AND price_lakh < 81 ORDER BY price_lakh ASC, listing_id ASC← Median Time to Resolve Incidents by Severity · Rate per Square Foot by Locality →