Myntra: Styles Most Shoppers Say Run Small

Medium
815.

Myntra: Styles Most Shoppers Say Run Small

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

ColumnType
style_idint
style_namevarchar
brandvarchar

Primary key: style_id.

One row per style.

Table: FitFeedback

ColumnTypeValues
feedback_idint
style_idint
user_idint
verdictenumruns_small, true_to_size, runs_large

Primary key: feedback_id.

One row per fit answer; a higher feedback_id is a later answer.

Example

The rows must come back in the order shown.

Style

style_idstyle_namebrand
1Roadster Slim Fit JeansRoadster
2HRX Running T-shirtHRX
3Anouk Printed KurtaAnouk

FitFeedback

feedback_idstyle_iduser_idverdict
111runs_small
212runs_small
313true_to_size
414runs_small
515runs_large
621runs_small
722runs_small
823true_to_size
924true_to_size
1023runs_small
4 more rows

Output

style_namebrandfeedbacksruns_small
Roadster Slim Fit JeansRoadster53
HRX Running T-shirtHRX43
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: 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

ColumnType
style_idint
style_namevarchar
brandvarchar

Primary key: style_id.

One row per style.

Table: FitFeedback

ColumnType
feedback_idint
style_idint
user_idint
verdictenum(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_idstyle_namebrand
1Roadster Slim Fit JeansRoadster
2HRX Running T-shirtHRX
3Anouk Printed KurtaAnouk

FitFeedback

feedback_idstyle_iduser_idverdict
111runs_small
212runs_small
313true_to_size
414runs_small
515runs_large
621runs_small
722runs_small
823true_to_size
924true_to_size
1023runs_small
1131runs_small
1231runs_small
1332runs_small
1433runs_small

Output

style_namebrandfeedbacksruns_small
Roadster Slim Fit JeansRoadster53
HRX Running T-shirtHRX43

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 ASC

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