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.