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:
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):