Razorpay: Diwali-Week Captured Volume Against Last Year's Diwali Week

Hard
918.

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

  • new when gmv_2024 is 0, else lost when gmv_2025 is 0;
  • otherwise grew when gmv_2025 is at least 1.2 × gmv_2024, shrank when it is at most 0.8 × gmv_2024, and flat in 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

ColumnType
merchant_idint
business_namevarchar
categoryvarchar

Primary key: merchant_id.

One row per merchant account on Razorpay.

Table: Festival

ColumnType
festival_yearint
diwali_datedate

Primary key: festival_year.

The date of Diwali (Lakshmi Puja) in each year — it falls on a different day every year.

Table: Payment

ColumnTypeValues
payment_idint
merchant_idint
amountint
created_atdatetime
statusenumcaptured, 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.

Example

The rows must come back in the order shown.

Merchant

merchant_idbusiness_namecategory
1Kirana Kartecommerce
2Urban Threadsecommerce
3FitFuel Labsfood
4BookNookeducation
5TrekNesttravel
6HomeBrew Coffeefood

Festival

festival_yeardiwali_date
20232023-11-12
20242024-11-01
20252025-10-20

Payment

payment_idmerchant_idamountcreated_atstatus
115000002024-10-26 00:00:00captured
213000002024-11-01 23:59:59captured
3110000002025-10-15 10:00:00captured
426000002024-10-28 12:00:00captured
522000002024-10-29 15:20:00refunded
624800002025-10-18 20:00:00captured
732500002025-10-20 18:30:00captured
834000002024-11-02 00:00:00captured
943500002024-10-30 11:00:00captured
1043500002025-10-27 11:00:00captured
5 more rows

Output

merchant_idbusiness_namegmv_2024gmv_2025yoy_changetrend
3FitFuel Labs0250000250000new
1Kirana Kart8000001000000200000grew
5TrekNest40000044000040000flat
2Urban Threads600000480000-120000shrank
4BookNook3500000-350000lost
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.

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:

  • new when gmv_2024 is 0, else lost when gmv_2025 is 0;
  • otherwise grew when gmv_2025 is at least 1.2 × gmv_2024, shrank when it is at most 0.8 × gmv_2024, and flat in 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

ColumnType
merchant_idint
business_namevarchar
categoryvarchar

Primary key: merchant_id.

One row per merchant account on Razorpay.

Table: Festival

ColumnType
festival_yearint
diwali_datedate

Primary key: festival_year.

The date of Diwali (Lakshmi Puja) in each year — it falls on a different day every year.

Table: Payment

ColumnType
payment_idint
merchant_idint
amountint
created_atdatetime
statusenum(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_idbusiness_namecategory
1Kirana Kartecommerce
2Urban Threadsecommerce
3FitFuel Labsfood
4BookNookeducation
5TrekNesttravel
6HomeBrew Coffeefood

Festival

festival_yeardiwali_date
20232023-11-12
20242024-11-01
20252025-10-20

Payment

payment_idmerchant_idamountcreated_atstatus
115000002024-10-26 00:00:00captured
213000002024-11-01 23:59:59captured
3110000002025-10-15 10:00:00captured
426000002024-10-28 12:00:00captured
522000002024-10-29 15:20:00refunded
624800002025-10-18 20:00:00captured
732500002025-10-20 18:30:00captured
834000002024-11-02 00:00:00captured
943500002024-10-30 11:00:00captured
1043500002025-10-27 11:00:00captured
1154000002024-10-27 09:15:00captured
1254400002025-10-16 13:40:00captured
135900002025-10-17 08:05:00failed
1461500002025-10-19 19:00:00failed
1562000002023-11-10 12:00:00captured

Output

merchant_idbusiness_namegmv_2024gmv_2025yoy_changetrend
3FitFuel Labs0250000250000new
1Kirana Kart8000001000000200000grew
5TrekNest40000044000040000flat
2Urban Threads600000480000-120000shrank
4BookNook3500000-350000lost

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_id

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

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