Myntra: Styles Marked Down by at Least 40% for EORS Day One

Medium
809.

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. 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

ColumnType
style_idint
style_namevarchar

Primary key: style_id.

One row per style.

Table: StylePrice

ColumnType
style_idint
effective_ondate
selling_priceint

Primary key (together): style_id, effective_on.

One row per price change: from effective_on the style sells at selling_price rupees.

Example

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

Style

style_idstyle_name
1Roadster Slim Fit Jeans
2HRX Running T-shirt
3Anouk Printed Kurta
4Dressberry Wrap Dress
5Sangria Anarkali Kurta

StylePrice

style_ideffective_onselling_price
12024-05-102000
12024-06-011200
22024-05-201000
22024-05-31900
22024-06-01600
32024-05-251500
32024-05-30800
32024-06-02500
42024-06-01999
52024-05-152500
2 more rows

Output

style_idstyle_nameprice_beforeprice_on_sale
1Roadster Slim Fit Jeans20001200
5Sangria Anarkali Kurta25001250
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 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

ColumnType
style_idint
style_namevarchar

Primary key: style_id.

One row per style.

Table: StylePrice

ColumnType
style_idint
effective_ondate
selling_priceint

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_idstyle_name
1Roadster Slim Fit Jeans
2HRX Running T-shirt
3Anouk Printed Kurta
4Dressberry Wrap Dress
5Sangria Anarkali Kurta

StylePrice

style_ideffective_onselling_price
12024-05-102000
12024-06-011200
22024-05-201000
22024-05-31900
22024-06-01600
32024-05-251500
32024-05-30800
32024-06-02500
42024-06-01999
52024-05-152500
52024-06-011250
52024-06-032500

Output

style_idstyle_nameprice_beforeprice_on_sale
1Roadster Slim Fit Jeans20001200
5Sangria Anarkali Kurta25001250

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 * 6

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