Razorpay: UPI Ticket Sizes in ₹1,000 Bands, Empty Bands Included
Razorpay's risk team sizes UPI traffic before tuning fraud rules: how many captured UPI payments fall in each ₹1,000 band of amount.
- Difficulty: Medium
- Topics: Aggregation, Joins, Subqueries
- Dialect: MySQL
- Problem: #912
Problem statement
Razorpay's risk team sizes UPI traffic before tuning fraud rules: how many captured UPI payments fall in each ₹1,000 band of amount. A chart with a missing bar is misleading, so every band is shown, including those with no payments.
Consider only payments with method = 'upi' and status = 'captured'. A band covers band_start <= amount < band_end, with band_end = band_start + 1000; bands start at 0 and go up to the band holding the largest such amount. For each band return band_start, band_end, payments (how many fall in it, 0 for an empty band) and total_amount (their sum, 0 for an empty band), ordered by band_start. If there is no captured UPI payment, return no rows.
A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.
Tables
Table: Payment
| Column | Type |
|---|---|
| payment_id | int |
| merchant_id | int |
| method | enum(upi, card, netbanking, wallet) |
| amount | decimal |
| status | enum(captured, failed) |
Primary key: payment_id.
One row per payment, amount in rupees. A UPI payment is below ₹1,00,000, the UPI per-transaction limit.
Examples
Example 1
Payment
| payment_id | merchant_id | method | amount | status |
|---|---|---|---|---|
| 1 | 11 | upi | 250 | captured |
| 2 | 11 | upi | 999.5 | captured |
| 3 | 12 | upi | 1000 | captured |
| 4 | 12 | upi | 4200 | captured |
| 5 | 13 | card | 2500 | captured |
| 6 | 13 | upi | 3100 | failed |
| 7 | 11 | upi | 1450.5 | captured |
| 8 | 14 | upi | 6000 | failed |
| 9 | 14 | netbanking | 9000 | captured |
Output
| band_start | band_end | payments | total_amount |
|---|---|---|---|
| 0 | 1000 | 2 | 1249.5 |
| 1000 | 2000 | 2 | 2450.5 |
| 2000 | 3000 | 0 | 0 |
| 3000 | 4000 | 0 | 0 |
| 4000 | 5000 | 1 | 4200 |
How to solve Razorpay: UPI Ticket Sizes in ₹1,000 Bands, Empty Bands Included
A histogram is a GROUP BY on the band, but grouping can only report bands that contain a row, and the chart needs the empty ones too. So the bands are generated first and the payments are joined onto them — a zero-filled histogram.
The recursive CTE starts at 0 and adds 1,000 while the next start is still at or below the largest captured UPI amount, so the last band is the one holding the maximum (9000.00 would start band 9000; 8999.50 stays in band 8000). Its anchor selects 0 only when MAX(amount) over the qualifying payments is not NULL, so it yields nothing when there are no qualifying payments, so the answer is empty rather than one empty band. The method and status filters live in ok, before both the maximum and the join — a failed ₹6,000 payment must not stretch the axis.
The LEFT JOIN keeps every band; COUNT(o.amount) counts only matched payments, so an empty band gets 0, and COALESCE(SUM(…), 0) turns its NULL sum into 0. The half-open test >= band_start AND < band_end puts ₹1,000.00 in the 1000 band and ₹999.50 in the 0 band.
The alternative uses the ₹1,00,000 UPI limit: at most 100 bands, so a digits × digits cross join is a big enough numbers table. It groups the payments by FLOOR(amount / 1000) once and LEFT JOINs those counts to the band numbers up to the largest one. Both read the payments once; the band list is tiny.
Reference solution (MySQL)
WITH RECURSIVE ok AS (
SELECT amount FROM Payment WHERE method = 'upi' AND status = 'captured'
), bands AS (
SELECT 0 AS band_start FROM (SELECT MAX(amount) AS top FROM ok) t WHERE t.top IS NOT NULL
UNION ALL
SELECT band_start + 1000 FROM bands
WHERE band_start + 1000 <= (SELECT MAX(amount) FROM ok)
)
SELECT b.band_start, b.band_start + 1000 AS band_end,
COUNT(o.amount) AS payments, COALESCE(SUM(o.amount), 0) AS total_amount
FROM bands b
LEFT JOIN ok o ON o.amount >= b.band_start AND o.amount < b.band_start + 1000
GROUP BY b.band_start
ORDER BY b.band_startAnother way
WITH ok AS (
SELECT amount FROM Payment WHERE method = 'upi' AND status = 'captured'
), digits AS (
SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
), n AS (
SELECT t.d * 10 + u.d AS k FROM digits t CROSS JOIN digits u
), counted AS (
SELECT FLOOR(amount / 1000) AS k, COUNT(*) AS payments, SUM(amount) AS total_amount
FROM ok GROUP BY FLOOR(amount / 1000)
)
SELECT n.k * 1000 AS band_start, n.k * 1000 + 1000 AS band_end,
COALESCE(c.payments, 0) AS payments, COALESCE(c.total_amount, 0) AS total_amount
FROM n
LEFT JOIN counted c ON c.k = n.k
WHERE n.k <= (SELECT FLOOR(MAX(amount) / 1000) FROM ok)
ORDER BY band_start← Razorpay: Expense Claims Not Approved by the Claimant's Manager · Razorpay: Settlements That Don't Match Their Line Items →