Compute weekly 3-week rolling sums in SQL

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.

Compute Weekly 3-Week Rolling Sums with Time Zone-Aware Week Bucketing

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.

Table transactions

column	type	constraints
user_id	INT	NOT NULL
event_ts	TIMESTAMPTZ	NOT NULL
amount	NUMERIC(12,2)	NOT NULL

Sample data:

amount	event_ts	user_id
12	2025-08-04 16:00:00+00	1
7.5	2025-08-05 18:30:00+00	2
5	2025-08-12 20:10:00+00	1
9	2025-08-19 15:05:00+00	3
11	2025-08-20 02:45:00+00	2
3	2025-08-26 12:00:00+00	1
8	2025-09-02 21:10:00+00	2
10	2025-09-09 14:25:00+00	3

Expected output

One row per LA-local week starting each Monday from 2025-08-04 through 2025-09-15, with the weekly sum of amounts and the 3-week rolling sum (current week plus prior two weeks). Weeks with no transactions appear with wk_amount = 0 and still contribute (as zero) to the rolling sum.

rolling_3wk_amount	week_start_date	wk_amount
19.5	2025-08-04	19.5
24.5	2025-08-11	5
44.5	2025-08-18	20
28	2025-08-25	3
31	2025-09-01	8
21	2025-09-08	10
18	2025-09-15	0

Hints

1. Use generate_series over a start and end DATE to build a weekly calendar, then left join aggregated transaction data onto it.
2. 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.