Parse Listing Titles Into Locality and BHK — SQL Problem
An old property portal stored only a free-text title such as 3 BHK Apartment - Baner, Pune or Studio Apartment - Wakad, Pune: the configuration, the…
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #361
Problem statement
An old property portal stored only a free-text title such as 3 BHK Apartment - Baner, Pune or Studio Apartment - Wakad, Pune: the configuration, the property type, then ' - ', the locality, a comma and the city. Spaces around the comma are not consistent.
Return one row per locality with locality (trimmed), listings, villas (titles whose type is Villa) and largest_bhk (the largest BHK number; a Studio counts as 0). Order by listings descending, then locality.
Tables
Table: PortalListing
| Column | Type |
|---|---|
| listing_id | int |
| title | varchar |
| price_lakh | int |
Primary key: listing_id.
title is '<n> BHK <Type> - <Locality>, <City>' or 'Studio <Type> - <Locality>, <City>'. Types are Apartment, Villa, Row House or Penthouse; localities contain no comma or hyphen.
Examples
Example 1
PortalListing
| listing_id | title | price_lakh |
|---|---|---|
| 1 | 3 BHK Apartment - Baner, Pune | 145 |
| 2 | 2 BHK Apartment - Baner , Pune | 92 |
| 3 | 4 BHK Villa - Baner,Pune | 310 |
| 4 | Studio Apartment - Wakad, Pune | 38 |
| 5 | 1 BHK Apartment - Wakad, Pune | 46 |
| 6 | Studio Apartment - Viman Nagar, Pune | 41 |
| 7 | 3 BHK Villa - Aundh, Pune | 260 |
| 8 | 2 BHK Row House - Aundh ,Pune | 175 |
Output
| locality | listings | villas | largest_bhk |
|---|---|---|---|
| Baner | 3 | 1 | 4 |
| Aundh | 2 | 1 | 3 |
| Wakad | 2 | 0 | 1 |
| Viman Nagar | 1 | 0 | 0 |
How to solve Parse Listing Titles Into Locality and BHK
All the facts are inside one text column, so the work is string parsing, then an ordinary GROUP BY on the parsed value. SUBSTRING_INDEX(title, ' - ', -1) keeps everything after the separator ('Baner , Pune'), SUBSTRING_INDEX(…, ',', 1) keeps what is before the comma ('Baner '), and TRIM removes the stray space. Without the TRIM, 'Baner' and 'Baner ' would be two groups — the example's inconsistent commas are there to catch that.
The BHK is the first word cast to a number, but a studio's first word is 'Studio', so a CASE gives it 0 before the cast. The villa count is a conditional sum on the type word, matched with its surrounding spaces and separator so a locality name can never match by accident.
MySQL lets you GROUP BY the alias; repeating the expression works everywhere. Parsing in a derived table first, with LOCATE and SUBSTRING instead of SUBSTRING_INDEX, is the alternative. Each title is parsed once.
Reference solution (MySQL)
SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' - ', -1), ',', 1)) AS locality,
COUNT(*) AS listings,
SUM(CASE WHEN title LIKE '% Villa - %' THEN 1 ELSE 0 END) AS villas,
MAX(CASE WHEN title LIKE 'Studio %' THEN 0 ELSE CAST(SUBSTRING_INDEX(title, ' ', 1) AS SIGNED) END) AS largest_bhk
FROM PortalListing
GROUP BY TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' - ', -1), ',', 1))
ORDER BY listings DESC, localityAnother way
SELECT locality, COUNT(*) AS listings, SUM(IF(kind = 'Villa', 1, 0)) AS villas, MAX(bhk) AS largest_bhk FROM (SELECT TRIM(SUBSTRING(title, LOCATE(' - ', title) + 3, LOCATE(',', title) - LOCATE(' - ', title) - 3)) AS locality, IF(LEFT(title, 6) = 'Studio', 0, CAST(LEFT(title, LOCATE(' ', title) - 1) AS SIGNED)) AS bhk, SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' - ', 1), ' ', -1) AS kind FROM PortalListing) t GROUP BY locality ORDER BY listings DESC, locality← Flats Owing More Than Their Wing's Average · Days From First Site Visit to Booking →