Myntra: The Best Price Box for Each Style Across Three Offers

Hard
817.

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. 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 when sale_price is at least coupon_min (equal counts); otherwise 0;
  • bank: bank_pct percent of sale_price, rounded down to a whole rupee, and never more than bank_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

ColumnType
style_idint
mrpint
discount_pctint
coupon_offint
coupon_minint
bank_pctint
bank_capint
insider_offint
floor_priceint

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.

Example

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

StyleOffer

style_idmrpdiscount_pctcoupon_offcoupon_minbank_pctbank_capinsider_offfloor_price
120004015099910200100600
2300050300150010250NULL900
3100030200799NULLNULL50600
4150020NULLNULL5100NULL1000
58000NULLNULLNULLNULLNULL500
6120050100599NULLNULL100400
7900601002991050030300
8100050NULLNULLNULLNULL100600

Output

style_idsale_pricebest_offerfinal_price
11200coupon1050
21500coupon1200
3700insider650
41200bank1140
5800none800
6600coupon500
7360coupon300
8500insider500
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: 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 when sale_price is at least coupon_min (equal counts); otherwise 0;
  • bank: bank_pct percent of sale_price, rounded down to a whole rupee, and never more than bank_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

ColumnType
style_idint
mrpint
discount_pctint
coupon_offint
coupon_minint
bank_pctint
bank_capint
insider_offint
floor_priceint

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_idmrpdiscount_pctcoupon_offcoupon_minbank_pctbank_capinsider_offfloor_price
120004015099910200100600
2300050300150010250NULL900
3100030200799NULLNULL50600
4150020NULLNULL5100NULL1000
58000NULLNULLNULLNULLNULL500
6120050100599NULLNULL100400
7900601002991050030300
8100050NULLNULLNULLNULL100600

Output

style_idsale_pricebest_offerfinal_price
11200coupon1050
21500coupon1200
3700insider650
41200bank1140
5800none800
6600coupon500
7360coupon300
8500insider500

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 best

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