Zomato: Build the URL Slug of Every Restaurant Page

Easy
1026.

Zomato: Build the URL Slug of Every Restaurant Page

EasyStrings

MySQL

Each restaurant on Zomato has a web page whose address is built from its name, locality and city. The SEO team is rebuilding the addresses after an import left names in mixed case with stray spaces at the ends.

Return restaurant_id and page_slug for every restaurant. The slug is the trimmed name, then the locality, then the city, each in lower case with every space replaced by a hyphen, joined by single hyphens. When locality is NULL the slug is just the name and the city. Return the rows in any order.

A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.

Tables

Table: Restaurant

ColumnType
restaurant_idint
namevarchar
localityvarchar
cityvarchar

Primary key: restaurant_id.

Names may carry spaces at either end but never two spaces in a row inside; locality is NULL when it was never filled in, and has no stray spaces.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Restaurant

restaurant_idnamelocalitycity
1Spice RouteKoramangalaBengaluru
2THE Momo Point Salt LakeKolkata
3 Pind DhabaNULLDelhi
4Cafe Mocha BayBandra WestMumbai
5dosa darbarBanjara HillsHyderabad

Output

restaurant_idpage_slug
1spice-route-koramangala-bengaluru
2the-momo-point-salt-lake-kolkata
3pind-dhaba-delhi
4cafe-mocha-bay-bandra-west-mumbai
5dosa-darbar-banjara-hills-hyderabad
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.

Zomato: Build the URL Slug of Every Restaurant Page

Each restaurant on Zomato has a web page whose address is built from its name, locality and city.

  • Difficulty: Easy
  • Topics: Strings
  • Dialect: MySQL
  • Problem: #1026

Problem statement

Each restaurant on Zomato has a web page whose address is built from its name, locality and city. The SEO team is rebuilding the addresses after an import left names in mixed case with stray spaces at the ends.

Return restaurant_id and page_slug for every restaurant. The slug is the trimmed name, then the locality, then the city, each in lower case with every space replaced by a hyphen, joined by single hyphens. When locality is NULL the slug is just the name and the city. Return the rows in any order.

A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.

Tables

Table: Restaurant

ColumnType
restaurant_idint
namevarchar
localityvarchar
cityvarchar

Primary key: restaurant_id.

Names may carry spaces at either end but never two spaces in a row inside; locality is NULL when it was never filled in, and has no stray spaces.

Examples

Example 1

Restaurant

restaurant_idnamelocalitycity
1Spice RouteKoramangalaBengaluru
2THE Momo Point Salt LakeKolkata
3 Pind DhabaNULLDelhi
4Cafe Mocha BayBandra WestMumbai
5dosa darbarBanjara HillsHyderabad

Output

restaurant_idpage_slug
1spice-route-koramangala-bengaluru
2the-momo-point-salt-lake-kolkata
3pind-dhaba-delhi
4cafe-mocha-bay-bandra-west-mumbai
5dosa-darbar-banjara-hills-hyderabad

How to solve Zomato: Build the URL Slug of Every Restaurant Page

The slug is three cleaned parts glued with hyphens. Each part gets the same treatment — lower case, spaces to hyphens — and the name additionally needs TRIM, because a leading or trailing space would otherwise become a hyphen at the start or a double hyphen in the middle of the slug.

The missing locality is the interesting case. CONCAT(name, '-', locality, '-', city) would return NULL for the whole slug when locality is NULL, which is MySQL's rule for CONCAT. CONCAT_WS('-', …) puts the separator only between the non-NULL arguments, so a NULL locality simply drops out and the slug becomes name-city with no stray hyphen. REPLACE and LOWER of a NULL are NULL too, so the locality stays NULL all the way into CONCAT_WS.

The alternative branches on the NULL with CASE, builds the spaced phrase first and replaces every space at once — it works because the parts themselves have no doubled spaces. Both are one pass over the table with string functions per row.

Reference solution (MySQL)

SELECT restaurant_id,
       CONCAT_WS('-',
                 LOWER(REPLACE(TRIM(name), ' ', '-')),
                 LOWER(REPLACE(locality, ' ', '-')),
                 LOWER(city)) AS page_slug
FROM Restaurant

Another way

SELECT restaurant_id,
       CASE WHEN locality IS NULL
            THEN LCASE(REPLACE(CONCAT(TRIM(name), ' ', city), ' ', '-'))
            ELSE LCASE(REPLACE(CONCAT(TRIM(name), ' ', locality, ' ', city), ' ', '-'))
       END AS page_slug
FROM Restaurant

← Zomato: Gold Members Yet to Use a Single Benefit · Zomato: Feeding India Cities That Raised ₹50,000 →