Deepest cut per product

ranking

Deepest cut per product

Myntra SQL Interview Question

Before a sale, Myntra's pricing team reviews the sharpest single cut each product has already taken this season. A product that has been cut hard once and still did not sell is a candidate for clearance rather than another discount, since repeating a cut that failed costs margin twice.

For each product that was ever cut, return the product_id, the changed_on date of its sharpest single fall, and the size of that fall as drop_amount. If a product took its sharpest cut more than once, report the first time it happened.

Asked of

  • Data Analyst
  • Business Analyst
  • BI Analyst
  • Product Analyst
  • Analytics Engineer
  • Data Engineer
  • Data Scientist

myntra_price_historyTable26 rows

Column NameType
change_idBIGINT
product_idVARCHAR
changed_onDATE
priceDOUBLE

myntra_price_historyExample Input

change_idproduct_idchanged_onprice
7101P1002024-04-012499
7102P1002024-04-052299
7103P1002024-04-092299
7104P1002024-04-141999
7105P1002024-04-202199
7106P1002024-04-262199
7107P1002024-04-301899
7114P3002024-04-031299
7115P3002024-04-081199
7116P3002024-04-131099
7117P3002024-04-191099
7118P3002024-04-241099
7119P3002024-04-291349

Example Output

product_idchanged_ondrop_amount
P1002024-04-14300
P3002024-04-08100

Explanation

P100 took two cuts of 300, on April 14 and April 30, so the earlier one is reported. P300 took two cuts of 100 each, and again the first one is returned.

The example above is a small slice of the data. Your query runs against the full tables.