Myntra: Product Display Names From Brand, Colour and Article
The product tile on the listing page shows a display name built from three catalogue fields.
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #801
Problem statement
The product tile on the listing page shows a display name built from three catalogue fields. Brand names were typed in by hand at onboarding and some carry stray spaces; some styles have no colour recorded yet.
For every live style (is_live = 1) return style_id and display_name: the brand with spaces removed from both ends, the colour, and the article type, joined by single spaces. When colour is NULL, leave it out (no double space). Order the rows by style_id.
A practice scenario set at Myntra: the tables and data are invented, and this is not a question from, or endorsed by, Myntra.
Tables
Table: Style
| Column | Type |
|---|---|
| style_id | int |
| brand | varchar |
| colour | varchar |
| article_type | varchar |
| is_live | bool |
Primary key: style_id.
brand may have leading or trailing spaces; colour and article_type never do. colour may be NULL.
Examples
Example 1
Style
| style_id | brand | colour | article_type | is_live |
|---|---|---|---|---|
| 1 | Roadster | Navy Blue | Slim Fit Jeans | 1 |
| 2 | HRX | Black | Running T-shirt | 1 |
| 3 | Anouk | NULL | Printed Kurta | 1 |
| 4 | Dressberry | Maroon | Wrap Dress | 0 |
| 5 | Mast & Harbour | Olive | Cargo Joggers | 1 |
Output
| style_id | display_name |
|---|---|
| 1 | Roadster Navy Blue Slim Fit Jeans |
| 2 | HRX Black Running T-shirt |
| 3 | Anouk Printed Kurta |
| 5 | Mast & Harbour Olive Cargo Joggers |
How to solve Myntra: Product Display Names From Brand, Colour and Article
Two string problems hide in this one. The first is the stray spaces around some brands: TRIM(brand) removes them from both ends, so ' HRX' and 'Mast & Harbour ' become clean words before anything is joined to them.
The second is the NULL colour. Plain CONCAT(TRIM(brand), ' ', colour, ' ', article_type) returns NULL for the whole name as soon as colour is NULL — MySQL's rule is that any NULL argument makes the result NULL — so the tile would show nothing. CONCAT_WS(' ', …) ("concat with separator") is built for this: it puts the separator only between the arguments that are not NULL, so a missing colour leaves Anouk Printed Kurta with single spaces.
The alternatives spell the same rule out by hand: a CASE that picks one of two CONCATs, or IFNULL(CONCAT(colour, ' '), ''), which turns a missing colour, together with its trailing space, into the empty string. The query is one pass over the styles plus a sort by the primary key.
Reference solution (MySQL)
SELECT style_id, CONCAT_WS(' ', TRIM(brand), colour, article_type) AS display_name
FROM Style
WHERE is_live = 1
ORDER BY style_idAnother way
SELECT style_id,
CASE WHEN colour IS NULL THEN CONCAT(TRIM(brand), ' ', article_type)
ELSE CONCAT(TRIM(brand), ' ', colour, ' ', article_type) END AS display_name
FROM Style WHERE is_live = 1 ORDER BY style_idAnother way
SELECT style_id, CONCAT(TRIM(brand), ' ', IFNULL(CONCAT(colour, ' '), ''), article_type) AS display_name FROM Style WHERE is_live = 1 ORDER BY style_id← Myntra: Most Common Return Reasons in 2024 · Myntra: Wishlisted Styles the Shopper Has Not Bought Yet →