Coding questions · Stripe
Write SQL to monitor weekly chargeback spikes
This question evaluates proficiency in PostgreSQL time-series analytics, specifically aggregations, joins across payments and chargebacks, handling late-arriving events, and calculating per-week metrics such as GMV, active merchants, and chargeback rates.
Write a single SQL query (PostgreSQL) to detect weekly chargeback spikes by country and industry. Week starts Monday (use date_trunc('week', ts) with ISO weeks). Schema and sample data:
Tables
- merchants(merchant_id INT, country_code TEXT, industry TEXT)
- payments(payment_id INT, merchant_id INT, created_at TIMESTAMP, amount_usd NUMERIC, status TEXT CHECK (status IN ('succeeded','failed')))
- chargebacks(chargeback_id INT, payment_id INT, created_at TIMESTAMP)
Sample rows
merchants
+-------------+--------------+----------+
| merchant_id | country_code | industry |
+-------------+--------------+----------+
| 1 | US | SaaS |
| 2 | US | Retail |
| 3 | DE | Retail |
| 4 | US | SaaS |
+-------------+--------------+----------+
payments
+------------+-------------+---------------------+------------+-----------+
| payment_id | merchant_id | created_at | amount_usd | status |
+------------+-------------+---------------------+------------+-----------+
| 101 | 1 | 2025-08-18 10:00:00 | 120.00 | succeeded |
| 102 | 1 | 2025-08-19 11:00:00 | 130.00 | succeeded |
| 103 | 2 | 2025-08-26 09:00:00 | 80.00 | failed |
| 104 | 2 | 2025-08-26 12:00:00 | 200.00 | succeeded |
| 105 | 3 | 2025-08-27 13:00:00 | 75.00 | succeeded |
| 106 | 4 | 2025-09-02 10:00:00 | 500.00 | succeeded |
| 107 | 1 | 2025-09-02 12:00:00 | 150.00 | succeeded |
+------------+-------------+---------------------+------------+-----------+
chargebacks
+----------------+------------+---------------------+
| chargeback_id | payment_id | created_at |
+----------------+------------+---------------------+
| 9001 | 101 | 2025-09-01 08:00:00 |
| 9002 | 104 | 2025-09-08 09:00:00 |
+----------------+------------+---------------------+
Task Return, for each (week_start, country_code, industry):
- gmv_usd: sum(amount_usd) of succeeded payments created in that week
- active_merchants: distinct merchants with ≥1 succeeded payment that week
- succeeded_cnt: count of succeeded payments that week
- cb_cnt_lagwin: chargebacks created during that week whose underlying payment was created in the lag window [week_start-14 days, week_start-7 days]
- cb_rate_lagwin: cb_cnt_lagwin / succeeded_cnt for payments in that lag window
- wow_cb_rate_pct_increase: week-over-week percentage change of cb_rate_lagwin
- top_merchant_share_gmv: in-week max(merchant_gmv)/gmv_usd Filter to rows where wow_cb_rate_pct_increase > 50% AND top_merchant_share_gmv < 0.40. Handle late-arriving chargebacks by joining chargebacks to payments via payment_id. Assume UTC throughout. Use CTEs; no temp tables. Also, based on the sample data, state which (week_start, country_code, industry) rows should be returned and why (briefly).
Detect Weekly Chargeback Spikes by Country and Industry
You are given three tables that track merchants, payments, and chargebacks. Write a single PostgreSQL query to detect weekly chargeback spikes by country and industry.
The week should start on Monday, using ISO weeks (i.e., use date_trunc('week', ts)). Assume all timestamps are in UTC.
Tables:
- merchants(merchant_id INT, country_code TEXT, industry TEXT)
- payments(payment_id INT, merchant_id INT, created_at TIMESTAMP, amount_usd NUMERIC, status TEXT CHECK (status IN ('succeeded','failed')))
- chargebacks(chargeback_id INT, payment_id INT, created_at TIMESTAMP)
For each combination of (week_start, country_code, industry), where week_start is date_trunc('week', created_at) of the payment, compute:
- gmv_usd: sum(amount_usd) of payments with status = 'succeeded' created in that week.
- active_merchants: count of distinct merchants with at least one succeeded payment in that week.
- succeeded_cnt: count of succeeded payments in that week.
- cb_cnt_lagwin: number of chargebacks created during that week (chargebacks.created_at in [week_start, week_start + 7 days)) whose underlying payment was created in the lag window [week_start - 14 days, week_start - 7 days).
- cb_rate_lagwin: cb_cnt_lagwin divided by the number of succeeded payments in that lag window for the same (country_code, industry). If there are no such succeeded payments, this rate should be NULL.
- wow_cb_rate_pct_increase: week-over-week percentage change in cb_rate_lagwin for the same (country_code, industry), i.e., 100 * (current_cb_rate_lagwin - previous_cb_rate_lagwin) / previous_cb_rate_lagwin. If the previous week’s rate is NULL or zero, the percentage change should be NULL.
- top_merchant_share_gmv: within that (week_start, country_code, industry), compute each merchant’s GMV in that week and return max(merchant_gmv) / gmv_usd.
Handle late-arriving chargebacks by joining chargebacks to payments via payment_id (do not assume chargeback created_at is close to payment created_at). Use CTEs only; do not use temporary tables.
Finally, filter the result to only rows where:
- wow_cb_rate_pct_increase > 50 AND
- top_merchant_share_gmv < 0.40
Return the columns: week_start, country_code, industry, gmv_usd, active_merchants, succeeded_cnt, cb_cnt_lagwin, cb_rate_lagwin, wow_cb_rate_pct_increase, top_merchant_share_gmv.
Based on the sample data provided, state which (week_start, country_code, industry) rows should be returned (if any) and explain briefly why.
Table merchants
| column | type | constraints |
|---|---|---|
| merchant_id | INT | PRIMARY KEY |
| country_code | VARCHAR(2) | NOT NULL |
| industry | VARCHAR(50) | NOT NULL |
Sample data:
| country_code | industry | merchant_id |
|---|---|---|
| US | SaaS | 1 |
| US | Retail | 2 |
| DE | Retail | 3 |
| US | SaaS | 4 |
Table payments
| column | type | constraints |
|---|---|---|
| payment_id | INT | PRIMARY KEY |
| merchant_id | INT | NOT NULL, FOREIGN KEY REFERENCES merchants(merchant_id) |
| created_at | TIMESTAMP | NOT NULL |
| amount_usd | NUMERIC(10,2) | NOT NULL |
| status | VARCHAR(10) | NOT NULL, CHECK (status IN ('succeeded','failed')) |
Sample data:
| amount_usd | created_at | merchant_id | payment_id | status |
|---|---|---|---|---|
| 120 | 2025-08-18 10:00:00 | 1 | 101 | succeeded |
| 130 | 2025-08-19 11:00:00 | 1 | 102 | succeeded |
| 80 | 2025-08-26 09:00:00 | 2 | 103 | failed |
| 200 | 2025-08-26 12:00:00 | 2 | 104 | succeeded |
| 75 | 2025-08-27 13:00:00 | 3 | 105 | succeeded |
| 500 | 2025-09-02 10:00:00 | 4 | 106 | succeeded |
| 150 | 2025-09-02 12:00:00 | 1 | 107 | succeeded |
Table chargebacks
| column | type | constraints |
|---|---|---|
| chargeback_id | INT | PRIMARY KEY |
| payment_id | INT | NOT NULL, FOREIGN KEY REFERENCES payments(payment_id) |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| chargeback_id | created_at | payment_id |
|---|---|---|
| 9001 | 2025-09-01 08:00:00 | 101 |
| 9002 | 2025-09-08 09:00:00 | 104 |
Expected output
The query should return one row per (week_start, country_code, industry) that satisfies both: (1) week-over-week chargeback rate (cb_rate_lagwin) increase > 50%, and (2) top_merchant_share_gmv < 0.40. For each such row, include: week_start (Monday of the ISO week based on payment.created_at), country_code, industry, gmv_usd, active_merchants, succeeded_cnt, cb_cnt_lagwin, cb_rate_lagwin, wow_cb_rate_pct_increase, and top_merchant_share_gmv.
For the provided sample data, no (week_start, country_code, industry) combination meets both criteria. Although the week starting 2025-09-01 for (US, SaaS) has a non-zero lag-window chargeback rate (1 chargeback over 2 succeeded payments in the lag window), its top_merchant_share_gmv is approximately 0.77 (> 0.40). All other groups have top_merchant_share_gmv = 1.0 and/or NULL chargeback rates. Therefore, the result set is empty.
Hints
- First build weekly aggregates of succeeded payments (GMV, counts, distinct merchants) per (week_start, country_code, industry) using date_trunc('week', created_at).
- Then compute chargebacks in the lag window by joining chargebacks to payments via payment_id, aggregate them per week, and use window functions to derive week-over-week changes in the lag-window chargeback rate.