Flipkart: Instant Discount From Each Bank Offer

Easy
655.

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

ColumnType
offer_idint
bank_namevarchar
discount_pctint
min_order_valueint
max_discountint

Primary key: offer_id.

One row per card offer; amounts in rupees.

Table: CardPayment

ColumnType
payment_idint
order_idint
offer_idint
amountint

Primary key: payment_id.

One row per card payment; offer_id is NULL when no offer was applied, and otherwise always names an offer.

Example

The rows must come back in the order shown.

BankOffer

offer_idbank_namediscount_pctmin_order_valuemax_discount
1HDFC Bank1050001500
2SBI Card103000750

CardPayment

payment_idorder_idoffer_idamount
19101112000
2910214990
39103125000
4910423000
59105NULL8000
6910629999

Output

payment_idamountinstant_discountamount_payable
112000120010800
2499004990
325000150023500
430003002700
5800008000
699997509249
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.

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

ColumnType
offer_idint
bank_namevarchar
discount_pctint
min_order_valueint
max_discountint

Primary key: offer_id.

One row per card offer; amounts in rupees.

Table: CardPayment

ColumnType
payment_idint
order_idint
offer_idint
amountint

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_idbank_namediscount_pctmin_order_valuemax_discount
1HDFC Bank1050001500
2SBI Card103000750

CardPayment

payment_idorder_idoffer_idamount
19101112000
2910214990
39103125000
4910423000
59105NULL8000
6910629999

Output

payment_idamountinstant_discountamount_payable
112000120010800
2499004990
325000150023500
430003002700
5800008000
699997509249

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_id

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