Parse Listing Titles Into Locality and BHK

Medium
361.

Parse Listing Titles Into Locality and BHK

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

ColumnType
listing_idint
titlevarchar
price_lakhint

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.

Example

The rows must come back in the order shown.

PortalListing

listing_idtitleprice_lakh
13 BHK Apartment - Baner, Pune145
22 BHK Apartment - Baner , Pune92
34 BHK Villa - Baner,Pune310
4Studio Apartment - Wakad, Pune38
51 BHK Apartment - Wakad, Pune46
6Studio Apartment - Viman Nagar, Pune41
73 BHK Villa - Aundh, Pune260
82 BHK Row House - Aundh ,Pune175

Output

localitylistingsvillaslargest_bhk
Baner314
Aundh213
Wakad201
Viman Nagar100
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.

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

ColumnType
listing_idint
titlevarchar
price_lakhint

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_idtitleprice_lakh
13 BHK Apartment - Baner, Pune145
22 BHK Apartment - Baner , Pune92
34 BHK Villa - Baner,Pune310
4Studio Apartment - Wakad, Pune38
51 BHK Apartment - Wakad, Pune46
6Studio Apartment - Viman Nagar, Pune41
73 BHK Villa - Aundh, Pune260
82 BHK Row House - Aundh ,Pune175

Output

localitylistingsvillaslargest_bhk
Baner314
Aundh213
Wakad201
Viman Nagar100

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, locality

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