Myntra: Live Styles With No Approved Catalogue Image

Easy
799.

Myntra: Live Styles With No Approved Catalogue Image

Catalogue quality control checks every product photo before it can be shown. A style that went live while none of its photos had passed QC shows a blank tile, and the catalogue team wants the list to fix.

Return style_id and style_name of every style with is_live = 1 that has no image with qc_status = 'approved' — whether it has no images at all or only rejected and pending ones. Styles that are not live never appear. Return the rows in any order.

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
style_namevarchar
brandvarchar
is_livebool

Primary key: style_id.

One row per style (a design, before colour and size variants). is_live is 1 when the style is visible to shoppers.

Table: StyleImage

ColumnTypeValues
image_idint
style_idint
qc_statusenumapproved, rejected, pending

Primary key: image_id.

One row per uploaded photo and its quality-check result.

Example

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

Style

style_idstyle_namebrandis_live
1Roadster Slim Fit JeansRoadster1
2HRX Running T-shirtHRX1
3Anouk Printed KurtaAnouk1
4Dressberry Wrap DressDressberry0
5Sangria Anarkali KurtaSangria1

StyleImage

image_idstyle_idqc_status
11approved
21rejected
32rejected
42pending
54pending
65approved

Output

style_idstyle_name
2HRX Running T-shirt
3Anouk Printed Kurta
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: Live Styles With No Approved Catalogue Image

Catalogue quality control checks every product photo before it can be shown.

  • Difficulty: Easy
  • Topics: Joins, Subqueries
  • Dialect: MySQL
  • Problem: #799

Problem statement

Catalogue quality control checks every product photo before it can be shown. A style that went live while none of its photos had passed QC shows a blank tile, and the catalogue team wants the list to fix.

Return style_id and style_name of every style with is_live = 1 that has no image with qc_status = 'approved' — whether it has no images at all or only rejected and pending ones. Styles that are not live never appear. Return the rows in any order.

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
style_namevarchar
brandvarchar
is_livebool

Primary key: style_id.

One row per style (a design, before colour and size variants). is_live is 1 when the style is visible to shoppers.

Table: StyleImage

ColumnType
image_idint
style_idint
qc_statusenum(approved, rejected, pending)

Primary key: image_id.

One row per uploaded photo and its quality-check result.

Examples

Example 1

Style

style_idstyle_namebrandis_live
1Roadster Slim Fit JeansRoadster1
2HRX Running T-shirtHRX1
3Anouk Printed KurtaAnouk1
4Dressberry Wrap DressDressberry0
5Sangria Anarkali KurtaSangria1

StyleImage

image_idstyle_idqc_status
11approved
21rejected
32rejected
42pending
54pending
65approved

Output

style_idstyle_name
2HRX Running T-shirt
3Anouk Printed Kurta

How to solve Myntra: Live Styles With No Approved Catalogue Image

This is an anti join: keep the live styles for which no approved image exists. NOT EXISTS says it directly — for each live style, the subquery looks for an image of that style whose status is approved, and the style is kept only when it finds none. A style with no images at all and a style with only rejected or pending images both pass, which is what the catalogue team wants.

The LEFT JOIN version is a common place to slip. The approval condition has to sit in the ON clause, so that only approved images are candidates for the match; a style with no such image then comes out with NULLs, and i.image_id IS NULL keeps it. Putting qc_status = 'approved' in WHERE instead would throw away every unmatched row and return nothing useful.

NOT IN works too because style_id in the image table is never NULL; if it could be, a single NULL would make NOT IN return no rows at all, which is why NOT EXISTS is the safer habit. All three are one scan of the styles with an index lookup into the images.

Reference solution (MySQL)

SELECT s.style_id, s.style_name
FROM Style s
WHERE s.is_live = 1
  AND NOT EXISTS (
    SELECT 1 FROM StyleImage i
    WHERE i.style_id = s.style_id AND i.qc_status = 'approved'
  )

Another way

SELECT s.style_id, s.style_name
FROM Style s
LEFT JOIN StyleImage i ON i.style_id = s.style_id AND i.qc_status = 'approved'
WHERE s.is_live = 1 AND i.image_id IS NULL

Another way

SELECT style_id, style_name FROM Style WHERE is_live = 1 AND style_id NOT IN (SELECT style_id FROM StyleImage WHERE qc_status = 'approved')

← Myntra: Orders Placed in the EORS Insider Early-Access Hours · Myntra: Most Common Return Reasons in 2024 →