Razorpay: Diwali-Week Captured Volume Against Last Year's Diwali Week
Every November Razorpay's merchant-success team asks how each merchant's festive rush compared with the year before.
- Difficulty: Hard
- Topics: Dates, Conditional Logic, Aggregation, Joins
- Dialect: MySQL
- Problem: #918
Problem statement
Every November Razorpay's merchant-success team asks how each merchant's festive rush compared with the year before. Diwali moves around the calendar, so the window is read from a Festival table, not from fixed dates.
A year's Diwali week is the seven days ending on that year's diwali_date: from diwali_date − 6 days 00:00:00 through diwali_date 23:59:59, inclusive. Only captured payments count — failed ones never moved money and refunded ones went back to the customer. Amounts are in paise.
For every merchant with captured volume in the Diwali week of 2024 or 2025 (or both), return merchant_id, business_name, gmv_2024, gmv_2025 (0 for a week with nothing captured), yoy_change (gmv_2025 − gmv_2024) and trend:
newwhengmv_2024is 0, elselostwhengmv_2025is 0;- otherwise
grewwhengmv_2025is at least 1.2 ×gmv_2024,shrankwhen it is at most 0.8 ×gmv_2024, andflatin between.
Order by yoy_change descending, then merchant_id. Other years in Festival are ignored.
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: Merchant
| Column | Type |
|---|---|
| merchant_id | int |
| business_name | varchar |
| category | varchar |
Primary key: merchant_id.
One row per merchant account on Razorpay.
Table: Festival
| Column | Type |
|---|---|
| festival_year | int |
| diwali_date | date |
Primary key: festival_year.
The date of Diwali (Lakshmi Puja) in each year — it falls on a different day every year.
Table: Payment
| Column | Type |
|---|---|
| payment_id | int |
| merchant_id | int |
| amount | int |
| created_at | datetime |
| status | enum(captured, failed, refunded) |
Primary key: payment_id.
One row per payment, amount in paise (₹1 = 100 paise). refunded payments were captured and later returned in full.
Examples
Example 1
Merchant
| merchant_id | business_name | category |
|---|---|---|
| 1 | Kirana Kart | ecommerce |
| 2 | Urban Threads | ecommerce |
| 3 | FitFuel Labs | food |
| 4 | BookNook | education |
| 5 | TrekNest | travel |
| 6 | HomeBrew Coffee | food |
Festival
| festival_year | diwali_date |
|---|---|
| 2023 | 2023-11-12 |
| 2024 | 2024-11-01 |
| 2025 | 2025-10-20 |
Payment
| payment_id | merchant_id | amount | created_at | status |
|---|---|---|---|---|
| 1 | 1 | 500000 | 2024-10-26 00:00:00 | captured |
| 2 | 1 | 300000 | 2024-11-01 23:59:59 | captured |
| 3 | 1 | 1000000 | 2025-10-15 10:00:00 | captured |
| 4 | 2 | 600000 | 2024-10-28 12:00:00 | captured |
| 5 | 2 | 200000 | 2024-10-29 15:20:00 | refunded |
| 6 | 2 | 480000 | 2025-10-18 20:00:00 | captured |
| 7 | 3 | 250000 | 2025-10-20 18:30:00 | captured |
| 8 | 3 | 400000 | 2024-11-02 00:00:00 | captured |
| 9 | 4 | 350000 | 2024-10-30 11:00:00 | captured |
| 10 | 4 | 350000 | 2025-10-27 11:00:00 | captured |
| 11 | 5 | 400000 | 2024-10-27 09:15:00 | captured |
| 12 | 5 | 440000 | 2025-10-16 13:40:00 | captured |
| 13 | 5 | 90000 | 2025-10-17 08:05:00 | failed |
| 14 | 6 | 150000 | 2025-10-19 19:00:00 | failed |
| 15 | 6 | 200000 | 2023-11-10 12:00:00 | captured |
Output
| merchant_id | business_name | gmv_2024 | gmv_2025 | yoy_change | trend |
|---|---|---|---|---|---|
| 3 | FitFuel Labs | 0 | 250000 | 250000 | new |
| 1 | Kirana Kart | 800000 | 1000000 | 200000 | grew |
| 5 | TrekNest | 400000 | 440000 | 40000 | flat |
| 2 | Urban Threads | 600000 | 480000 | -120000 | shrank |
| 4 | BookNook | 350000 | 0 | -350000 | lost |
How to solve Razorpay: Diwali-Week Captured Volume Against Last Year's Diwali Week
A year-on-year comparison is only fair when both sides cover the same occasion, and Diwali falls on a different date every year — 1 November in 2024, 20 October in 2025 in the example. So the windows come from data: for each of the two years, DATE_SUB(diwali_date, INTERVAL 6 DAY) is the first day and DATE_ADD(diwali_date, INTERVAL 1 DAY) an exclusive end, which keeps a payment stamped 23:59:59 on Diwali day and drops one at 00:00:00 the next morning without any DATE() call on the column. A payment on the same calendar dates as last year's week (BookNook's 27 October 2025) is outside this year's week, which is the mistake fixed dates would make.
Joining captured payments to the two window rows tags each payment with its festival year — the windows cannot overlap, so a payment is counted once. One grouping per merchant then pivots the years into columns with conditional sums; ELSE 0 makes a year with no captured payment a 0 rather than a NULL, which the new/lost labels and yoy_change need. A merchant appears only if some captured payment fell in a window, which is exactly the population asked for: failed and refunded payments, and payments in 2023's week, never reach the grouping.
The trend is checked in order — new and lost first, since 0 would otherwise pass as shrank — and the 20% thresholds are compared in integers (5 × gmv_2025 ≥ 6 × gmv_2024), so a merchant exactly 20% down, like Urban Threads, is shrank with no floating-point doubt.
The first alternative aggregates per (merchant, year) and stitches the two years back together with a list of merchants and two LEFT JOINs — a full outer join, which MySQL lacks. The second computes each year with a correlated subquery per merchant and finds the window with DATEDIFF(diwali_date, created_at) BETWEEN 0 AND 6; it reads the payments once per merchant and year, fine with an index on (merchant_id, created_at), while the main query reads them once.
Reference solution (MySQL)
WITH win AS (
SELECT festival_year,
DATE_SUB(diwali_date, INTERVAL 6 DAY) AS starts_on,
DATE_ADD(diwali_date, INTERVAL 1 DAY) AS ends_before
FROM Festival
WHERE festival_year IN (2024, 2025)
), g AS (
SELECT p.merchant_id,
SUM(CASE WHEN w.festival_year = 2024 THEN p.amount ELSE 0 END) AS gmv_2024,
SUM(CASE WHEN w.festival_year = 2025 THEN p.amount ELSE 0 END) AS gmv_2025
FROM Payment p
JOIN win w ON p.created_at >= w.starts_on AND p.created_at < w.ends_before
WHERE p.status = 'captured'
GROUP BY p.merchant_id
)
SELECT m.merchant_id, m.business_name, g.gmv_2024, g.gmv_2025,
g.gmv_2025 - g.gmv_2024 AS yoy_change,
CASE WHEN g.gmv_2024 = 0 THEN 'new'
WHEN g.gmv_2025 = 0 THEN 'lost'
WHEN 5 * g.gmv_2025 >= 6 * g.gmv_2024 THEN 'grew'
WHEN 5 * g.gmv_2025 <= 4 * g.gmv_2024 THEN 'shrank'
ELSE 'flat' END AS trend
FROM g
JOIN Merchant m ON m.merchant_id = g.merchant_id
ORDER BY yoy_change DESC, m.merchant_idAnother way
WITH y AS (
SELECT p.merchant_id, f.festival_year, SUM(p.amount) AS gmv
FROM Payment p
JOIN Festival f ON f.festival_year IN (2024, 2025)
AND p.created_at >= DATE_SUB(f.diwali_date, INTERVAL 6 DAY)
AND p.created_at < DATE_ADD(f.diwali_date, INTERVAL 1 DAY)
WHERE p.status = 'captured'
GROUP BY p.merchant_id, f.festival_year
), k AS (
SELECT DISTINCT merchant_id FROM y
), t AS (
SELECT k.merchant_id, COALESCE(a.gmv, 0) AS gmv_2024, COALESCE(b.gmv, 0) AS gmv_2025
FROM k
LEFT JOIN y a ON a.merchant_id = k.merchant_id AND a.festival_year = 2024
LEFT JOIN y b ON b.merchant_id = k.merchant_id AND b.festival_year = 2025
)
SELECT t.merchant_id, m.business_name, t.gmv_2024, t.gmv_2025, t.gmv_2025 - t.gmv_2024 AS yoy_change,
IF(t.gmv_2024 = 0, 'new', IF(t.gmv_2025 = 0, 'lost',
IF(t.gmv_2025 * 5 >= t.gmv_2024 * 6, 'grew', IF(t.gmv_2025 * 5 <= t.gmv_2024 * 4, 'shrank', 'flat')))) AS trend
FROM t JOIN Merchant m ON m.merchant_id = t.merchant_id
ORDER BY yoy_change DESC, t.merchant_idAnother way
SELECT merchant_id, business_name, gmv_2024, gmv_2025, gmv_2025 - gmv_2024 AS yoy_change,
CASE WHEN gmv_2024 = 0 THEN 'new' WHEN gmv_2025 = 0 THEN 'lost'
WHEN gmv_2025 * 5 >= gmv_2024 * 6 THEN 'grew' WHEN gmv_2025 * 5 <= gmv_2024 * 4 THEN 'shrank'
ELSE 'flat' END AS trend
FROM (
SELECT m.merchant_id, m.business_name,
(SELECT COALESCE(SUM(p.amount), 0) FROM Payment p JOIN Festival f ON f.festival_year = 2024
WHERE p.merchant_id = m.merchant_id AND p.status = 'captured'
AND DATEDIFF(f.diwali_date, p.created_at) BETWEEN 0 AND 6) AS gmv_2024,
(SELECT COALESCE(SUM(p.amount), 0) FROM Payment p JOIN Festival f ON f.festival_year = 2025
WHERE p.merchant_id = m.merchant_id AND p.status = 'captured'
AND DATEDIFF(f.diwali_date, p.created_at) BETWEEN 0 AND 6) AS gmv_2025
FROM Merchant m
) x
WHERE gmv_2024 > 0 OR gmv_2025 > 0
ORDER BY yoy_change DESC, merchant_id← Razorpay: Merchants Whose Live Methods Differ From Their Request · Razorpay: Orders Rescued by Switching Payment Method →