Write rolling-window SQL over weekly cohorts
Company: Chime
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You have two tables: users and transactions. Write a single SQL query (use CTEs) to produce, for each calendar week and user, weekly_revenue, a 4-week rolling sum of revenue, week-over-week percent change, and the rank of each user within the week by rolling revenue (ties allowed). Use date_trunc('week', ts) for week starts (weeks start Monday, timestamps are UTC). Additionally, return first_purchase_date for each user and the number of days between first and second purchase. Return columns: week_start, user_id, weekly_revenue, rolling_4w_revenue, wow_change_pct, rank_in_week, first_purchase_date, days_to_second_purchase. Requirements: (a) Use SUM(...) OVER with a ROWS-based rolling window equivalent to 4 weeks; (b) Use LAG to compute week-over-week change and filter to rows where weekly_revenue decreased by at least 20% vs prior week; (c) Use DENSE_RANK to rank users by rolling_4w_revenue per week and output only the top 3 ranks per week; (d) Ensure users with fewer than 2 purchases still appear with NULL days_to_second_purchase. Schema and small sample data:
users
user_id | signup_date | country
1 | 2025-06-02 | US
2 | 2025-06-15 | CA
3 | 2025-07-01 | US
4 | 2025-07-20 | UK
transactions
tx_id | user_id | ts | amount
t1 | 1 | 2025-07-07 10:00:00 | 50
t2 | 1 | 2025-07-12 09:00:00 | 30
t3 | 1 | 2025-07-21 14:00:00 | 20
t4 | 2 | 2025-07-09 12:00:00 | 40
t5 | 2 | 2025-07-28 08:00:00 | 60
t6 | 3 | 2025-07-15 11:00:00 | 70
t7 | 3 | 2025-07-22 17:00:00 | 20
t8 | 3 | 2025-08-05 10:00:00 | 50
t9 | 1 | 2025-08-10 10:10:00 | 90
t10 | 2 | 2025-08-16 13:00:00 | 30
Notes: Treat missing prior-week revenue as NULL when computing wow_change_pct; assume a transaction implies a purchase.
Overview: This question evaluates proficiency with SQL window functions and CTEs for time-based cohort aggregation, including rolling ROWS-based sums, LAG-based week-over-week percent change, DENSE_RANK ranking, and handling first- and second-purchase calculations.
Read the full Chime Data Scientist interview experience this question came from
You are given two tables, users and transactions. Write a single SQL query (you may use multiple CTEs) that produces, for each calendar week and user, the following metrics:
- weekly_revenue: total revenue for that user in that calendar week
- rolling_4w_revenue: a 4-week rolling sum of weekly_revenue for that user, using a ROWS-based window
- wow_change_pct: week-over-week percent change in weekly_revenue vs the prior (previous-row) week for that user
- rank_in_week: the DENSE_RANK of each user within the same week, ordered by rolling_4w_revenue (ties allowed)
- first_purchase_date: date of the user's first purchase (based on transactions.ts)
- days_to_second_purchase: number of days between the first and second purchase; NULL if the user has fewer than 2 purchases
Use date_trunc('week', ts) as the week start (weeks start on Monday; timestamps are UTC). Treat a missing prior-week revenue as NULL when computing wow_change_pct.
Return the following columns:
- week_start (timestamp, from date_trunc('week', ts))
- user_id
- weekly_revenue
- rolling_4w_revenue
- wow_change_pct
- rank_in_week
- first_purchase_date
- days_to_second_purchase
Additional requirements:
(a) Use SUM(...) OVER with a ROWS-based rolling window equivalent to 4 weeks (i.e., over the current row and the previous 3 weekly rows per user).
(b) Use LAG to compute week-over-week change and filter the final result to only rows where weekly_revenue decreased by at least 20% versus the prior week (wow_change_pct <= -0.20).
(c) Use DENSE_RANK to rank users by rolling_4w_revenue per week and output only rows where rank_in_week is in the top 3 ranks for that week.
(d) Ensure users with fewer than 2 purchases still appear in the output (for weeks where they have revenue) with days_to_second_purchase = NULL.
Write the query for a PostgreSQL-like SQL dialect. Format week_start as YYYY-MM-DD HH24:MI:SS in the final output.
Tables
users(user_id INT, signup_date DATE, country VARCHAR(3))
transactions(tx_id VARCHAR(10), user_id INT, ts TIMESTAMP, amount DECIMAL(10,2))
Hints
- First aggregate to a per-user, per-week level using date_trunc('week', ts) before applying window functions.
- Use window functions: SUM(...) OVER (ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) for the rolling 4-week sum, LAG for week-over-week change, DENSE_RANK for weekly ranking, and a separate CTE with ROW_NUMBER to derive first and second purchase dates per user.