Ready-to-Move 2 BHK Listings Under ₹80 Lakh

Easy
348.

Ready-to-Move 2 BHK Listings Under ₹80 Lakh

EasyBasics

MySQL

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

ColumnTypeValues
listing_idint
localityvarchar
bhkint
price_lakhint
possessionenumready, under_construction
statusenumactive, 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).

Example

The rows must come back in the order shown.

Listing

listing_idlocalitybhkprice_lakhpossessionstatus
201Baner278readyactive
202Wakad264readyactive
203Wakad372readyactive
204Kharadi280readyactive
205Hinjewadi258under_constructionactive
206Aundh279readysold
207Kothrud281readyactive
208Hadapsar264readyactive

Output

listing_idlocalityprice_lakh
202Wakad64
208Hadapsar64
201Baner78
204Kharadi80
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.

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

ColumnType
listing_idint
localityvarchar
bhkint
price_lakhint
possessionenum(ready, under_construction)
statusenum(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_idlocalitybhkprice_lakhpossessionstatus
201Baner278readyactive
202Wakad264readyactive
203Wakad372readyactive
204Kharadi280readyactive
205Hinjewadi258under_constructionactive
206Aundh279readysold
207Kothrud281readyactive
208Hadapsar264readyactive

Output

listing_idlocalityprice_lakh
202Wakad64
208Hadapsar64
201Baner78
204Kharadi80

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_id

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

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