Compute weekly 3-week rolling sums in SQL
Company: Thumbtack
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: HR Screen
Using PostgreSQL, write a single query that outputs, for each calendar week in a given range, the sum of amounts in that week and a rolling sum over the current week plus the prior two full weeks (3-week window). Requirements: (1) Define weeks by America/Los_Angeles local time, Monday–Sunday, and compute week_start_date as the LA-local Monday date for each week. (2) Generate a complete weekly calendar (include weeks with zero activity). (3) Treat missing weeks as zero in the rolling sum. (4) Ignore late-arriving data; use event_ts as the source of truth. (5) No temp tables; use CTEs, generate_series, and window functions. Provide the final SELECT and a brief explanation of how you handle time zone conversion before week-bucketing.
Schema and sample data:
Table: transactions
Columns:
- user_id INT
- event_ts TIMESTAMPTZ -- stored in UTC
- amount NUMERIC(12,2)
Sample rows (UTC):
+---------+-------------------------+--------+
| user_id | event_ts | amount |
+---------+-------------------------+--------+
| 1 | 2025-08-04 16:00:00+00 | 12.00 |
| 2 | 2025-08-05 18:30:00+00 | 7.50 |
| 1 | 2025-08-12 20:10:00+00 | 5.00 |
| 3 | 2025-08-19 15:05:00+00 | 9.00 |
| 2 | 2025-08-20 02:45:00+00 | 11.00 |
| 1 | 2025-08-26 12:00:00+00 | 3.00 |
| 2 | 2025-09-02 21:10:00+00 | 8.00 |
| 3 | 2025-09-09 14:25:00+00 | 10.00 |
+---------+-------------------------+--------+
Output columns (one row per week in the range 2025-08-04 through 2025-09-15, LA-local):
- week_start_date DATE (LA-local Monday)
- wk_amount NUMERIC(12,2)
- rolling_3wk_amount NUMERIC(12,2)
Edge cases to handle: (a) weeks with no transactions should appear with wk_amount = 0, (b) events near midnight must be assigned to the correct LA-local week.
Overview: This question evaluates time zone–aware date bucketing, gapless calendar generation, and rolling-window aggregation skills, demonstrating competency in SQL-based data manipulation and temporal reasoning.
Using PostgreSQL, write a single query that outputs, for each calendar week in the LA-local range from 2025-08-04 through 2025-09-15 (inclusive, Mondays), the sum of transaction amounts in that week and a rolling sum over the current week plus the prior two full weeks (a 3-week window).
Requirements:
1. Define weeks by America/Los_Angeles local time, Monday–Sunday, and compute week_start_date as the LA-local Monday date for each week.
2. Generate a complete weekly calendar from 2025-08-04 through 2025-09-15 (one row per Monday), including weeks with zero activity.
3. Treat missing weeks as zero in the rolling sum.
4. Ignore late-arriving data; use event_ts as the source of truth.
5. Do not use temp tables; use CTEs, generate_series, and window functions.
Output columns:
- week_start_date DATE (LA-local Monday marking the start of the week)
- wk_amount NUMERIC(12,2) (sum of amounts in that LA-local week)
- rolling_3wk_amount NUMERIC(12,2) (sum over the current week and the two preceding weeks, with missing weeks treated as 0)
Also provide, in a brief comment in your final query, an explanation of how you handle time zone conversion before assigning events to weeks.
Tables
transactions(user_id INT, event_ts TIMESTAMPTZ, amount NUMERIC(12,2))
Hints
- Use generate_series over a start and end DATE to build a weekly calendar, then left join aggregated transaction data onto it.
- Convert event_ts to America/Los_Angeles with AT TIME ZONE before using date_trunc('week') so events near midnight are assigned to the correct local week.