Myntra: Styles Most Shoppers Say Run Small — SQL Problem
After delivery, shoppers rate how a style fits, and they can change their answer later.
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #815
Problem statement
After delivery, shoppers rate how a style fits, and they can change their answer later. The size-chart team wants the styles where most shoppers say it runs small, to add a "size up" note.
Only each shopper's latest verdict on a style counts — the one with the highest feedback_id for that shopper and style. For every style with at least 4 such verdicts, where more than half of them are runs_small, return style_name, brand, feedbacks (the number of latest verdicts) and runs_small (how many of them are runs_small). Order by runs_small descending, then by style_id ascending.
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 |
Primary key: style_id.
One row per style.
Table: FitFeedback
| Column | Type |
|---|---|
| feedback_id | int |
| style_id | int |
| user_id | int |
| verdict | enum(runs_small, true_to_size, runs_large) |
Primary key: feedback_id.
One row per fit answer; a higher feedback_id is a later answer.
Examples
Example 1
Style
| style_id | style_name | brand |
|---|---|---|
| 1 | Roadster Slim Fit Jeans | Roadster |
| 2 | HRX Running T-shirt | HRX |
| 3 | Anouk Printed Kurta | Anouk |
FitFeedback
| feedback_id | style_id | user_id | verdict |
|---|---|---|---|
| 1 | 1 | 1 | runs_small |
| 2 | 1 | 2 | runs_small |
| 3 | 1 | 3 | true_to_size |
| 4 | 1 | 4 | runs_small |
| 5 | 1 | 5 | runs_large |
| 6 | 2 | 1 | runs_small |
| 7 | 2 | 2 | runs_small |
| 8 | 2 | 3 | true_to_size |
| 9 | 2 | 4 | true_to_size |
| 10 | 2 | 3 | runs_small |
| 11 | 3 | 1 | runs_small |
| 12 | 3 | 1 | runs_small |
| 13 | 3 | 2 | runs_small |
| 14 | 3 | 3 | runs_small |
Output
| style_name | brand | feedbacks | runs_small |
|---|---|---|---|
| Roadster Slim Fit Jeans | Roadster | 5 | 3 |
| HRX Running T-shirt | HRX | 4 | 3 |
How to solve Myntra: Styles Most Shoppers Say Run Small
Counting the raw table would let one shopper vote several times. Style 3 has four runs_small rows, but two come from shopper 1, so it really has three voters and misses the threshold of four; style 2 has two small answers out of four on first reading, but shopper 3 later changed to runs_small, which makes it three of four.
So the first step keeps each shopper's latest answer per style: the row whose feedback_id equals the correlated MAX(feedback_id) for the same style and shopper. The remaining rows are joined to Style and grouped per style; COUNT(*) is the number of voters and SUM(CASE WHEN verdict = 'runs_small' THEN 1 ELSE 0 END) the small votes. Both conditions are about whole groups, so they go in HAVING: at least four voters, and 2 * small > total, which is "more than half" without division (exactly half does not qualify).
The order is small votes descending, then style_id, so equal counts come out in a fixed order. The alternative picks the latest answer with ROW_NUMBER() partitioned by style and shopper, newest first, and filters the counts in an outer query — the same result, with one sort instead of a subquery per row.
Reference solution (MySQL)
WITH latest AS (
SELECT f.style_id, f.verdict
FROM FitFeedback f
WHERE f.feedback_id = (SELECT MAX(g.feedback_id) FROM FitFeedback g
WHERE g.style_id = f.style_id AND g.user_id = f.user_id)
)
SELECT s.style_name, s.brand,
COUNT(*) AS feedbacks,
SUM(CASE WHEN l.verdict = 'runs_small' THEN 1 ELSE 0 END) AS runs_small
FROM Style s
JOIN latest l ON l.style_id = s.style_id
GROUP BY s.style_id, s.style_name, s.brand
HAVING COUNT(*) >= 4 AND 2 * SUM(CASE WHEN l.verdict = 'runs_small' THEN 1 ELSE 0 END) > COUNT(*)
ORDER BY runs_small DESC, s.style_id ASCAnother way
WITH ranked AS (
SELECT style_id, verdict,
ROW_NUMBER() OVER (PARTITION BY style_id, user_id ORDER BY feedback_id DESC) AS rn
FROM FitFeedback
), counts AS (
SELECT style_id, COUNT(*) AS feedbacks, SUM(IF(verdict = 'runs_small', 1, 0)) AS small
FROM ranked WHERE rn = 1 GROUP BY style_id
)
SELECT s.style_name, s.brand, c.feedbacks, c.small AS runs_small
FROM counts c JOIN Style s ON s.style_id = c.style_id
WHERE c.feedbacks >= 4 AND c.small > c.feedbacks / 2
ORDER BY c.small DESC, c.style_id← Myntra: Monthly Net Revenue After Refunds in 2024 · Myntra: Each Brand's Share of Its Category in the EORS →