Myntra: Product Display Names From Brand, Colour and Article

Easy
801.

Myntra: Product Display Names From Brand, Colour and Article

EasyStrings

MySQL

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

ColumnType
style_idint
brandvarchar
colourvarchar
article_typevarchar
is_livebool

Primary key: style_id.

brand may have leading or trailing spaces; colour and article_type never do. colour may be NULL.

Example

The rows must come back in the order shown.

Style

style_idbrandcolourarticle_typeis_live
1RoadsterNavy BlueSlim Fit Jeans1
2 HRXBlackRunning T-shirt1
3AnoukNULLPrinted Kurta1
4DressberryMaroonWrap Dress0
5Mast & Harbour OliveCargo Joggers1

Output

style_iddisplay_name
1Roadster Navy Blue Slim Fit Jeans
2HRX Black Running T-shirt
3Anouk Printed Kurta
5Mast & Harbour Olive Cargo Joggers
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.

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

ColumnType
style_idint
brandvarchar
colourvarchar
article_typevarchar
is_livebool

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_idbrandcolourarticle_typeis_live
1RoadsterNavy BlueSlim Fit Jeans1
2 HRXBlackRunning T-shirt1
3AnoukNULLPrinted Kurta1
4DressberryMaroonWrap Dress0
5Mast & Harbour OliveCargo Joggers1

Output

style_iddisplay_name
1Roadster Navy Blue Slim Fit Jeans
2HRX Black Running T-shirt
3Anouk Printed Kurta
5Mast & 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_id

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

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