Myntra: Styles Marked Down by at Least 40% for EORS Day One
The pricing desk keeps a log of price changes; a price stays in force from its effective_on date until the style's next change.
- Difficulty: Medium
- Topics: Subqueries, Dates, Joins
- Dialect: MySQL
- Problem: #809
Problem statement
The pricing desk keeps a log of price changes; a price stays in force from its effective_on date until the style's next change. For the sale's launch banner they want the styles whose price on 2024-06-01, the first day of the sale, is at least 40% below the price in force on 2024-05-31.
The price in force on a day is the row with the latest effective_on on or before that day. A style needs a price in force on both days to be considered. Return style_id, style_name, price_before (in force on 2024-05-31) and price_on_sale (in force on 2024-06-01) for every style where price_on_sale is at most 60% of price_before. 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 |
Primary key: style_id.
One row per style.
Table: StylePrice
| Column | Type |
|---|---|
| style_id | int |
| effective_on | date |
| selling_price | int |
Primary key: style_id, effective_on.
One row per price change: from effective_on the style sells at selling_price rupees.
Examples
Example 1
Style
| style_id | style_name |
|---|---|
| 1 | Roadster Slim Fit Jeans |
| 2 | HRX Running T-shirt |
| 3 | Anouk Printed Kurta |
| 4 | Dressberry Wrap Dress |
| 5 | Sangria Anarkali Kurta |
StylePrice
| style_id | effective_on | selling_price |
|---|---|---|
| 1 | 2024-05-10 | 2000 |
| 1 | 2024-06-01 | 1200 |
| 2 | 2024-05-20 | 1000 |
| 2 | 2024-05-31 | 900 |
| 2 | 2024-06-01 | 600 |
| 3 | 2024-05-25 | 1500 |
| 3 | 2024-05-30 | 800 |
| 3 | 2024-06-02 | 500 |
| 4 | 2024-06-01 | 999 |
| 5 | 2024-05-15 | 2500 |
| 5 | 2024-06-01 | 1250 |
| 5 | 2024-06-03 | 2500 |
Output
| style_id | style_name | price_before | price_on_sale |
|---|---|---|---|
| 1 | Roadster Slim Fit Jeans | 2000 | 1200 |
| 5 | Sangria Anarkali Kurta | 2500 | 1250 |
How to solve Myntra: Styles Marked Down by at Least 40% for EORS Day One
A price log answers "what was the price on day D?" with the change that was most recent on D: the row with the latest effective_on that is not after D. Style 2 changed price on 20 May and again on 31 May, so on 31 May it sold at 900, not 1000 — comparing against the older price would wrongly put it in the banner.
The solution finds that row twice, as two CTEs: for each style, the row whose effective_on equals the correlated MAX(effective_on) on or before the day. A style with no row on or before 31 May (style 4, launched on sale day) has no before_sale row, and the inner join drops it, as the statement requires. A change after 1 June (style 3's on 2 June) is ignored. Since (style_id, effective_on) is the key, each CTE has exactly one row per style.
The markdown test price_on_sale <= 0.6 * price_before is written as price_on_sale * 10 <= price_before * 6, all integers, so a cut of exactly 40% (2000 → 1200) is kept with no floating-point doubt. The alternative ranks each style's rows newest first with ROW_NUMBER() under each cut-off and keeps rank 1.
Reference solution (MySQL)
WITH before_sale AS (
SELECT p.style_id, p.selling_price
FROM StylePrice p
WHERE p.effective_on = (SELECT MAX(q.effective_on) FROM StylePrice q
WHERE q.style_id = p.style_id AND q.effective_on <= '2024-05-31')
), on_sale AS (
SELECT p.style_id, p.selling_price
FROM StylePrice p
WHERE p.effective_on = (SELECT MAX(q.effective_on) FROM StylePrice q
WHERE q.style_id = p.style_id AND q.effective_on <= '2024-06-01')
)
SELECT s.style_id, s.style_name, b.selling_price AS price_before, o.selling_price AS price_on_sale
FROM Style s
JOIN before_sale b ON b.style_id = s.style_id
JOIN on_sale o ON o.style_id = s.style_id
WHERE o.selling_price * 10 <= b.selling_price * 6Another way
WITH b AS (
SELECT style_id, selling_price, ROW_NUMBER() OVER (PARTITION BY style_id ORDER BY effective_on DESC) AS rn
FROM StylePrice WHERE effective_on <= '2024-05-31'
), o AS (
SELECT style_id, selling_price, ROW_NUMBER() OVER (PARTITION BY style_id ORDER BY effective_on DESC) AS rn
FROM StylePrice WHERE effective_on <= '2024-06-01'
)
SELECT s.style_id, s.style_name, b.selling_price AS price_before, o.selling_price AS price_on_sale
FROM Style s JOIN b ON b.style_id = s.style_id AND b.rn = 1
JOIN o ON o.style_id = s.style_id AND o.rn = 1
WHERE o.selling_price <= 0.6 * b.selling_price← Myntra: Creator Store Commission for October 2024 · Myntra: Wishlist-to-Purchase Conversion by Article Type →