Flipkart: Shopsy Reseller Store Handles

Easy
654.

Flipkart: Shopsy Reseller Store Handles

EasyStrings

MySQL

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

ColumnType
reseller_idint
store_namevarchar
cityvarchar

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.

Example

The rows must come back in the order shown.

ShopsyReseller

reseller_idstore_namecity
1Meera SareesJaipur
2 Kids Corner Pune
3GLOW BEAUTY HUBKochi
4Rakhi GiftsNULL
5trendy kurtis Bengaluru

Output

reseller_idhandle
1meera-sarees-JAI
2kids-corner-PUN
3glow-beauty-hub-KOC
4rakhi-gifts
5trendy-kurtis-BEN
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.

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

ColumnType
reseller_idint
store_namevarchar
cityvarchar

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_idstore_namecity
1Meera SareesJaipur
2 Kids Corner Pune
3GLOW BEAUTY HUBKochi
4Rakhi GiftsNULL
5trendy kurtis Bengaluru

Output

reseller_idhandle
1meera-sarees-JAI
2kids-corner-PUN
3glow-beauty-hub-KOC
4rakhi-gifts
5trendy-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_id

Another way

SELECT reseller_id, CONCAT_WS('-', LOWER(REPLACE(TRIM(store_name), ' ', '-')), UPPER(SUBSTRING(city, 1, 3))) AS handle FROM ShopsyReseller ORDER BY reseller_id

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