Myntra: The Best Price Box for Each Style Across Three Offers
A product page's Best Price box shows the lowest price a shopper can reach.
- Difficulty: Hard
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #817
Problem statement
A product page's Best Price box shows the lowest price a shopper can reach. Only one extra offer may be stacked on the sale price, so the box must work out which of a style's three offers — a coupon, a bank offer and an Insider reward — saves the most, and what it leaves the shopper paying.
For each style: sale_price is mrp * (100 - discount_pct) / 100. The offers are worth, in rupees:
- coupon:
coupon_off, but only whensale_priceis at leastcoupon_min(equal counts); otherwise 0; - bank:
bank_pctpercent ofsale_price, rounded down to a whole rupee, and never more thanbank_cap; - insider:
insider_off.
An offer whose columns are NULL is not available and is worth 0. Return style_id, sale_price, best_offer — the name ('coupon', 'bank' or 'insider') of the offer worth the most, ties going to coupon, then bank, then insider, and 'none' when every offer is worth 0 — and final_price: sale_price minus that offer's value, but never below the brand's floor_price (a sale price already under the floor stays as it is, since no offer applies then). 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: StyleOffer
| Column | Type |
|---|---|
| style_id | int |
| mrp | int |
| discount_pct | int |
| coupon_off | int |
| coupon_min | int |
| bank_pct | int |
| bank_cap | int |
| insider_off | int |
| floor_price | int |
Primary key: style_id.
One row per live style. coupon_off/coupon_min are both NULL when no coupon applies, bank_pct/bank_cap both NULL when no bank offer runs; insider_off is NULL for styles outside the Insider programme. mrp is a multiple of 100 and discount_pct of 5.
Examples
Example 1
StyleOffer
| style_id | mrp | discount_pct | coupon_off | coupon_min | bank_pct | bank_cap | insider_off | floor_price |
|---|---|---|---|---|---|---|---|---|
| 1 | 2000 | 40 | 150 | 999 | 10 | 200 | 100 | 600 |
| 2 | 3000 | 50 | 300 | 1500 | 10 | 250 | NULL | 900 |
| 3 | 1000 | 30 | 200 | 799 | NULL | NULL | 50 | 600 |
| 4 | 1500 | 20 | NULL | NULL | 5 | 100 | NULL | 1000 |
| 5 | 800 | 0 | NULL | NULL | NULL | NULL | NULL | 500 |
| 6 | 1200 | 50 | 100 | 599 | NULL | NULL | 100 | 400 |
| 7 | 900 | 60 | 100 | 299 | 10 | 500 | 30 | 300 |
| 8 | 1000 | 50 | NULL | NULL | NULL | NULL | 100 | 600 |
Output
| style_id | sale_price | best_offer | final_price |
|---|---|---|---|
| 1 | 1200 | coupon | 1050 |
| 2 | 1500 | coupon | 1200 |
| 3 | 700 | insider | 650 |
| 4 | 1200 | bank | 1140 |
| 5 | 800 | none | 800 |
| 6 | 600 | coupon | 500 |
| 7 | 360 | coupon | 300 |
| 8 | 500 | insider | 500 |
How to solve Myntra: The Best Price Box for Each Style Across Three Offers
This is row-wise arithmetic: everything the box needs is in one row, so no join or grouping is involved; the difficulty is in the NULLs, the caps and the order of the bounds. GREATEST and LEAST compare values across columns of a row the way MAX and MIN compare down a column.
First each offer becomes rupees. The coupon is worth coupon_off only when the sale price reaches coupon_min — style 3's 700 does not reach 799. The bank offer is LEAST(FLOOR(sale * pct / 100), cap): style 2's 10% of 1,500 is 150, under its 250 cap, while style 7's cap is generous but 10% of 360 is only 36. A NULL anywhere makes LEAST and GREATEST return NULL in MySQL, so each value is wrapped in COALESCE(…, 0) — without it style 3, with no bank offer, would lose its whole row's answer.
GREATEST(coupon, bank, insider) is the saving; the name is found by testing the values against it in the tie order, so style 1's coupon of 150 beats a bank 120 and Insider 100, and style 6's coupon and Insider tie at 100 and the coupon wins. Style 5 has nothing available, so its best value is 0 and the name is 'none'.
The final price has two bounds. GREATEST(sale - best, floor) stops an offer from cutting below the brand's floor — style 7's coupon would take 360 down to 260, but the floor holds it at 300. Then LEAST(sale, …) stops the floor from raising a price: style 8 already sells at 500 under a 600 floor, and the outer LEAST keeps 500. The order matters — swapping the two bounds would charge style 8 more than its sale price. The alternative spells every comparison out with nested IFs and integer DIV, exactly what GREATEST/LEAST save you from writing. Both are a single scan of the table.
Reference solution (MySQL)
WITH priced AS (
SELECT style_id, floor_price, mrp * (100 - discount_pct) DIV 100 AS sale_price, coupon_off, coupon_min,
bank_pct, bank_cap, insider_off
FROM StyleOffer
), valued AS (
SELECT style_id, sale_price, floor_price,
CASE WHEN coupon_off IS NOT NULL AND sale_price >= coupon_min THEN coupon_off ELSE 0 END AS coupon_val,
COALESCE(LEAST(FLOOR(sale_price * bank_pct / 100), bank_cap), 0) AS bank_val,
COALESCE(insider_off, 0) AS insider_val
FROM priced
), best AS (
SELECT style_id, sale_price, floor_price, coupon_val, bank_val, insider_val,
GREATEST(coupon_val, bank_val, insider_val) AS best_val
FROM valued
)
SELECT style_id, sale_price,
CASE WHEN best_val = 0 THEN 'none'
WHEN coupon_val = best_val THEN 'coupon'
WHEN bank_val = best_val THEN 'bank'
ELSE 'insider' END AS best_offer,
LEAST(sale_price, GREATEST(sale_price - best_val, floor_price)) AS final_price
FROM bestAnother way
SELECT style_id, sale_price,
IF(c = 0 AND b = 0 AND i = 0, 'none',
IF(c >= b AND c >= i, 'coupon', IF(b >= i, 'bank', 'insider'))) AS best_offer,
IF(sale_price <= floor_price, sale_price,
IF(sale_price - IF(c >= b AND c >= i, c, IF(b >= i, b, i)) < floor_price, floor_price,
sale_price - IF(c >= b AND c >= i, c, IF(b >= i, b, i)))) AS final_price
FROM (
SELECT style_id, sale_price, floor_price,
IF(coupon_min IS NULL OR sale_price < coupon_min, 0, coupon_off) AS c,
IF(bank_pct IS NULL, 0,
IF((sale_price * bank_pct) DIV 100 > bank_cap, bank_cap, (sale_price * bank_pct) DIV 100)) AS b,
IFNULL(insider_off, 0) AS i
FROM (
SELECT style_id, floor_price, coupon_off, coupon_min, bank_pct, bank_cap, insider_off,
(mrp * (100 - discount_pct)) DIV 100 AS sale_price
FROM StyleOffer
) p
) v← Myntra: Each Brand's Share of Its Category in the EORS · Myntra: EORS First-Time Buyers Who Ordered Again Within 90 Days →