Flipkart: Shopsy Reseller Store Handles — SQL Problem
Shopsy resellers share their store with a short handle in WhatsApp links.
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #654
Problem statement
Shopsy resellers share their store with a short handle in WhatsApp links. Store names were typed by the resellers themselves, so some carry stray spaces at either end and odd capitals.
Build each reseller's handle: the store name with leading and trailing spaces removed, in lower case, with every space replaced by a hyphen, then a hyphen and the first three letters of the city in upper case. If city is NULL, the handle is the name part alone. Return reseller_id and handle, ordered by reseller_id.
A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.
Tables
Table: ShopsyReseller
| Column | Type |
|---|---|
| reseller_id | int |
| store_name | varchar |
| city | varchar |
Primary key: reseller_id.
One row per reseller. Words inside store_name are separated by single spaces; city is NULL when the reseller skipped it.
Examples
Example 1
ShopsyReseller
| reseller_id | store_name | city |
|---|---|---|
| 1 | Meera Sarees | Jaipur |
| 2 | Kids Corner | Pune |
| 3 | GLOW BEAUTY HUB | Kochi |
| 4 | Rakhi Gifts | NULL |
| 5 | trendy kurtis | Bengaluru |
Output
| reseller_id | handle |
|---|---|
| 1 | meera-sarees-JAI |
| 2 | kids-corner-PUN |
| 3 | glow-beauty-hub-KOC |
| 4 | rakhi-gifts |
| 5 | trendy-kurtis-BEN |
How to solve Flipkart: Shopsy Reseller Store Handles
The handle is a chain of string functions, and the order of the steps matters. TRIM must run before REPLACE, or the spaces at either end would turn into leading and trailing hyphens. LOWER can run before or after the replacement, since hyphens have no case. The suffix is '-' followed by UPPER(LEFT(city, 3)).
The NULL city is handled by a property of CONCAT: if any argument is NULL, the result is NULL. So CONCAT('-', UPPER(LEFT(city, 3))) is NULL exactly when the city is missing, and IFNULL(…, '') turns that into an empty suffix. CONCAT_WS('-', name, suffix) does the same in one call because it skips NULL arguments along with their separator, and the CASE alternative spells the two cases out.
Each row is transformed independently, so the query is a single scan; the ORDER BY on the key fixes the output order.
Reference solution (MySQL)
SELECT reseller_id,
CONCAT(LOWER(REPLACE(TRIM(store_name), ' ', '-')), IFNULL(CONCAT('-', UPPER(LEFT(city, 3))), '')) AS handle
FROM ShopsyReseller
ORDER BY reseller_idAnother way
SELECT reseller_id, CONCAT_WS('-', LOWER(REPLACE(TRIM(store_name), ' ', '-')), UPPER(SUBSTRING(city, 1, 3))) AS handle FROM ShopsyReseller ORDER BY reseller_idAnother way
SELECT reseller_id,
CASE WHEN city IS NULL THEN REPLACE(LCASE(TRIM(store_name)), ' ', '-')
ELSE CONCAT(REPLACE(LCASE(TRIM(store_name)), ' ', '-'), '-', UCASE(LEFT(city, 3))) END AS handle
FROM ShopsyReseller ORDER BY reseller_id← Flipkart: Pay Later Accounts Spending Past Their Limit · Flipkart: Instant Discount From Each Bank Offer →