Write SQL to monitor weekly chargeback spikes
Company: Stripe
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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).
Overview: 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.
Read the full Stripe Data Scientist interview experience this question came from
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.
Tables
merchants(merchant_id INT, country_code VARCHAR(2), industry VARCHAR(50))
payments(payment_id INT, merchant_id INT, created_at TIMESTAMP, amount_usd NUMERIC(10,2), status VARCHAR(10))
chargebacks(chargeback_id INT, payment_id INT, created_at TIMESTAMP)
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.