
Deepest cut per product
mediumDeepest 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 Name | Type |
|---|---|
| change_id | BIGINT |
| product_id | VARCHAR |
| changed_on | DATE |
| price | DOUBLE |
myntra_price_historyExample Input
| change_id | product_id | changed_on | price |
|---|---|---|---|
| 7101 | P100 | 2024-04-01 | 2499 |
| 7102 | P100 | 2024-04-05 | 2299 |
| 7103 | P100 | 2024-04-09 | 2299 |
| 7104 | P100 | 2024-04-14 | 1999 |
| 7105 | P100 | 2024-04-20 | 2199 |
| 7106 | P100 | 2024-04-26 | 2199 |
| 7107 | P100 | 2024-04-30 | 1899 |
| 7114 | P300 | 2024-04-03 | 1299 |
| 7115 | P300 | 2024-04-08 | 1199 |
| 7116 | P300 | 2024-04-13 | 1099 |
| 7117 | P300 | 2024-04-19 | 1099 |
| 7118 | P300 | 2024-04-24 | 1099 |
| 7119 | P300 | 2024-04-29 | 1349 |
Example Output
| product_id | changed_on | drop_amount |
|---|---|---|
| P100 | 2024-04-14 | 300 |
| P300 | 2024-04-08 | 100 |
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.
Company
Myntra
Difficulty
medium
Topic
ranking
Language
SQL
Your query
Starting the SQL engine...