Razorpay mid NULLIFAVGSUMCOALESCE

For each merchant_name in razorpay_merchant_txns, compute avg_fee as the average gateway_fee across transactions where a fee genuinely applies — treating any gateway_fee of -1 as not a real fee — rounded to 2 decimal places, and total_refunded as the sum of refund_amount (treating merchants with no refunds at all as 0 instead of NULL). Return merchant_name, avg_fee, and total_refunded, sorted by merchant_name.

Razorpay · Merchant Settlement Fees — practice this real-world SQL scenario live in your browser.

📖 Story Razorpay · Merchant Settlement Fees

Tuesday morning at Razorpay's Merchant Finance team. razorpay_merchant_txns logs every transaction attempt for a merchant, along with gateway_fee (what Razorpay charged for processing it) and refund_amount (money sent back to the customer, if any). Failed transactions never actually got charged a fee — but a legacy import logged gateway_fee = -1 for every one of them instead of leaving it NULL, a placeholder someone chose years ago. refund_amount, on the other hand, is a genuine NULL whenever a transaction was never refunded — nobody invented a placeholder for that column. The team wants each merchant's real average fee on transactions that actually incurred one, plus their total refunded amount, without the -1 placeholder being mistaken for an actual fee.

🎯 Your Mission

📋 Table Structure

🗂 razorpay_merchant_txns

id INTEGER 1

merchant_name TEXT Craftsy Home

txn_status TEXT success

gateway_fee REAL 12.5

refund_amount REAL 200

⚡ Step-by-Step Walkthrough

Look at the raw transactions, including the -1 placeholder fees

query.sql

SELECT *
FROM razorpay_merchant_txns
ORDER BY merchant_name, id;

💡 Explanation

razorpay_merchant_txns has one row per transaction attempt, not one row per merchant — merchant_name repeats across every transaction, and Craftsy Home has 6 rows, Vivid Mart has 5, Orbit Foods has 4.
Every failed transaction has gateway_fee = -1 — Craftsy Home has two of them, Vivid Mart has one. A failed transaction was never actually charged anything; -1 is a placeholder a legacy import chose, not a genuine fee value and not a genuine NULL either.
refund_amount tells a different story: it's a real NULL on every transaction that was never refunded, and a real positive number on the handful that were (one each for Craftsy Home, Vivid Mart, and Orbit Foods). Nobody invented a fake number for 'no refund' — that column already uses NULL the way NULL is meant to be used.
Nothing is aggregated yet — this step only sets up the contrast the next two steps depend on: gateway_fee hides a fake NULL as -1, while refund_amount uses a real one, and they need to be handled completely differently.

See the trap: AVG(gateway_fee) treats -1 like a real fee

query.sql

SELECT merchant_name,
       ROUND(AVG(gateway_fee), 2) AS avg_fee_wrong,
       SUM(refund_amount) AS total_refunded
FROM razorpay_merchant_txns
GROUP BY merchant_name
ORDER BY merchant_name;

💡 Explanation

AVG(gateway_fee) sums every value in the group and divides by the count, and -1 is just an ordinary number to it — Craftsy Home's two failed transactions pull -1 and -1 into that sum right alongside four genuine fees.
Craftsy Home's avg_fee_wrong comes out to 8.92, when every one of its four real fees (12.50, 15.00, 10.00, 18.00) is above 10 — a merchant with consistently decent fees ends up looking like it barely charges anything.
Orbit Foods has zero failed transactions, so avg_fee_wrong for it is already correct at 29.75 — this bug is completely invisible on any merchant without a failed transaction, which is exactly the kind of thing that passes every spot check until the wrong merchant happens to look at their own numbers.
total_refunded is already correct here, with no special handling at all — SUM(refund_amount) just skips the genuine NULLs on its own, which is the whole point: a real NULL never needed fixing in the first place.

Fix it: NULLIF turns the fake -1 into a real NULL

query.sql

SELECT merchant_name,
       ROUND(AVG(NULLIF(gateway_fee, -1)), 2) AS avg_fee,
       COALESCE(SUM(refund_amount), 0) AS total_refunded
FROM razorpay_merchant_txns
GROUP BY merchant_name
ORDER BY merchant_name;

💡 Explanation

NULLIF(gateway_fee, -1) checks each value against -1: if it matches, the expression returns NULL instead; if it doesn't, the original gateway_fee passes through untouched. Every real fee is unaffected — only the placeholder rows change.
AVG() already knows how to skip a genuine NULL, so once NULLIF turns -1 into NULL, Craftsy Home's average is computed from exactly its four real fees: (12.50+15.00+10.00+18.00)/4 = 13.88, not 8.92.
No WHERE txn_status = 'success' filter was needed anywhere — NULLIF does the same job at the value level that a status filter would have done at the row level, and it works even on a table that never had a status column to filter on.
COALESCE(SUM(refund_amount), 0) is added defensively for a merchant with zero refunds at all, where SUM would otherwise return NULL instead of 0 — none of these three merchants happens to hit that case, but the query is written to handle it correctly if a fourth merchant with no refunds ever shows up.

▶ Practice this live ← Browse all questions