← Stripe Interview Insights

Stripe·Data Scientist·Technical Phone Screen·Senior

SeniorPrefer not to say
May 2026Remote

Summary

Stripe Data Scientist interview, technical screen focused entirely on a single gnarly SQL problem. The question was dense enough that I think they were testing whether you'd panic or methodically work through it. No behavioral stuff at all, just pure SQL craft.

Questions Asked (1)

Q1

Write a single PostgreSQL query using CTEs to detect weekly chargeback spikes by country and industry. You need to compute GMV, active merchants, succeeded payment counts, a lagged chargeback rate, week-over-week change in that rate, and top merchant GMV concentration, then filter to rows where the WoW chargeback rate increase exceeds 50% and the top merchant share is below 40%. Weeks start Monday using ISO week truncation. Handle late-arriving chargebacks by joining through payment_id rather than chargeback timestamp alone.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs
Author's notes

This one took me a minute to even parse.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Structure the query as a series of CTEs that progressively aggregate data at the country-industry-week grain, then use window functions to compute lagged metrics and week-over-week changes. Join chargebacks to payments via payment_id to correctly attribute late-arriving chargebacks to the original payment's week. Finally, filter for weeks where the chargeback rate increase exceeds 50% and top merchant concentration is below 40%.

Pro tip: Always clarify the definition of 'chargeback rate' (e.g., chargebacks per succeeded payment or per GMV) and confirm the week truncation method (ISO week starting Monday) with the interviewer before writing the query. Explicitly state your assumptions about late-arriving data and how the join through payment_id handles it.

1. Define the base aggregation

Create a CTE that aggregates payments and chargebacks at the country-industry-week level, using ISO week truncation (date_trunc('week', payment_date)) and joining chargebacks via payment_id to attribute them to the payment's week.

2. Compute core metrics

In the base CTE, calculate GMV (sum of payment amounts), active merchants (count distinct merchant_id), succeeded payment counts, and chargeback counts. Then compute the chargeback rate as chargebacks divided by succeeded payments.

3. Calculate lagged metrics and WoW change

Use window functions (LAG) partitioned by country and industry, ordered by week, to get the previous week's chargeback rate. Compute the week-over-week change as (current_rate - lagged_rate) / lagged_rate.

4. Compute top merchant concentration

For each country-industry-week, calculate the share of GMV from the top merchant. This can be done with a subquery or window function to rank merchants by GMV and then take the max share.

5. Apply filters and finalize

Filter the results to rows where the WoW chargeback rate increase exceeds 50% (i.e., (current_rate - lagged_rate)/lagged_rate > 0.5) and the top merchant share is below 40%. Ensure the final output includes all required metrics.

Key Points to Mention

  • Use of CTEs for modular and readable query structure
  • ISO week truncation with date_trunc('week', ...) to ensure weeks start on Monday
  • Joining chargebacks through payment_id to correctly handle late-arriving chargebacks
  • Window functions (LAG) for computing lagged chargeback rate and WoW change
  • Calculation of top merchant GMV concentration using window functions or subqueries
  • Filtering criteria: WoW increase > 50% and top merchant share < 40%

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.