Compute weekly 3-week rolling sums in SQL

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during HR Screen rounds at Thumbtack.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Thumbtack during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview