Flipkart: Instant Discount From Each Bank Offer
At checkout a card payment may carry a bank offer: a percentage off the amount, only when the amount reaches the offer's minimum, and never more than the…
- Difficulty: Easy
- Topics: Joins, Conditional Logic
- Dialect: MySQL
- Problem: #655
Problem statement
At checkout a card payment may carry a bank offer: a percentage off the amount, only when the amount reaches the offer's minimum, and never more than the offer's cap. Finance reconciles what each payment actually got.
For every payment return payment_id, amount, instant_discount and amount_payable (amount minus the discount). The discount is 0 when offer_id is NULL or the amount is below min_order_value; otherwise it is amount * discount_pct / 100, capped at max_discount (use the smaller of the two). Order by payment_id.
A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.
Tables
Table: BankOffer
| Column | Type |
|---|---|
| offer_id | int |
| bank_name | varchar |
| discount_pct | int |
| min_order_value | int |
| max_discount | int |
Primary key: offer_id.
One row per card offer; amounts in rupees.
Table: CardPayment
| Column | Type |
|---|---|
| payment_id | int |
| order_id | int |
| offer_id | int |
| amount | int |
Primary key: payment_id.
One row per card payment; offer_id is NULL when no offer was applied, and otherwise always names an offer.
Examples
Example 1
BankOffer
| offer_id | bank_name | discount_pct | min_order_value | max_discount |
|---|---|---|---|---|
| 1 | HDFC Bank | 10 | 5000 | 1500 |
| 2 | SBI Card | 10 | 3000 | 750 |
CardPayment
| payment_id | order_id | offer_id | amount |
|---|---|---|---|
| 1 | 9101 | 1 | 12000 |
| 2 | 9102 | 1 | 4990 |
| 3 | 9103 | 1 | 25000 |
| 4 | 9104 | 2 | 3000 |
| 5 | 9105 | NULL | 8000 |
| 6 | 9106 | 2 | 9999 |
Output
| payment_id | amount | instant_discount | amount_payable |
|---|---|---|---|
| 1 | 12000 | 1200 | 10800 |
| 2 | 4990 | 0 | 4990 |
| 3 | 25000 | 1500 | 23500 |
| 4 | 3000 | 300 | 2700 |
| 5 | 8000 | 0 | 8000 |
| 6 | 9999 | 750 | 9249 |
How to solve Flipkart: Instant Discount From Each Bank Offer
Each payment needs its offer's terms beside it, and payments without an offer still belong in the report, so the payments are LEFT JOINed to the offers. Then the discount is a CASE: nothing when there is no offer (o.offer_id IS NULL) or the amount is below the minimum, otherwise the percentage amount * discount_pct / 100 limited by LEAST(…, max_discount).
The boundary is inclusive — an amount equal to min_order_value qualifies — so the zero branch tests amount < min_order_value. Computing the discount inside a derived table lets the outer query subtract it without repeating the logic.
The alternative relies on NULL logic: with no offer, p.amount >= o.min_order_value is NULL, and IF(NULL, …, 0) takes the else branch, so a separate NULL test is not needed. Both are one pass over the payments with a key lookup into the offers.
Reference solution (MySQL)
SELECT payment_id, amount, instant_discount, amount - instant_discount AS amount_payable
FROM (
SELECT p.payment_id, p.amount,
CASE WHEN o.offer_id IS NULL OR p.amount < o.min_order_value THEN 0
ELSE LEAST(p.amount * o.discount_pct / 100, o.max_discount) END AS instant_discount
FROM CardPayment p
LEFT JOIN BankOffer o ON o.offer_id = p.offer_id
) t
ORDER BY payment_idAnother way
SELECT p.payment_id, p.amount,
IF(p.amount >= o.min_order_value, LEAST(p.amount * o.discount_pct / 100, o.max_discount), 0) AS instant_discount,
p.amount - IF(p.amount >= o.min_order_value, LEAST(p.amount * o.discount_pct / 100, o.max_discount), 0) AS amount_payable
FROM CardPayment p LEFT JOIN BankOffer o ON o.offer_id = p.offer_id
ORDER BY p.payment_id← Flipkart: Shopsy Reseller Store Handles · Flipkart: Refund SLA Breaches by Product Category →