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
| Column | Type |
|---|---|
| style_id | int |
| style_name | varchar |
| brand | varchar |
| is_live | bool |
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
| Column | Type |
|---|---|
| image_id | int |
| style_id | int |
| qc_status | enum(approved, rejected, pending) |
Primary key: image_id.
One row per uploaded photo and its quality-check result.
Examples
Example 1
Style
| style_id | style_name | brand | is_live |
|---|---|---|---|
| 1 | Roadster Slim Fit Jeans | Roadster | 1 |
| 2 | HRX Running T-shirt | HRX | 1 |
| 3 | Anouk Printed Kurta | Anouk | 1 |
| 4 | Dressberry Wrap Dress | Dressberry | 0 |
| 5 | Sangria Anarkali Kurta | Sangria | 1 |
StyleImage
| image_id | style_id | qc_status |
|---|---|---|
| 1 | 1 | approved |
| 2 | 1 | rejected |
| 3 | 2 | rejected |
| 4 | 2 | pending |
| 5 | 4 | pending |
| 6 | 5 | approved |
Output
| style_id | style_name |
|---|---|
| 2 | HRX Running T-shirt |
| 3 | Anouk 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 NULLAnother 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 →