Compute weekly 3-week rolling sums in SQL

Write one PostgreSQL query against the transactions table described below. For every Monday in the America/Los_Angeles week range 2025-09-01 through 2025-10-13 inclusive, return one row containing that week's total amount and a trailing three-week total.

The output columns are:

• week_start_date DATE: the LA-local Monday on which the week begins.
• wk_amount NUMERIC(12,2): total amount for all transactions whose LA-local timestamp falls within that Monday–Sunday week.
• rolling_3wk_amount NUMERIC(12,2): the current week's wk_amount plus the previous two weeks' wk_amount values. Weeks with no transactions are treated as 0.

Requirements:

• Assign every transaction to a week using America/Los_Angeles local time, not the UTC date.
• Weeks run Monday through Sunday; week_start_date is the LA-local Monday.
• Produce a row for every Monday in the range, including weeks with zero activity.
• Missing weeks must be treated as 0 in the rolling sum.
• Use event_ts as the only source of truth; ignore any late-arrival or inserted-at timestamp.
• Use CTEs, generate_series, and window functions. Do not create temporary tables.
• Add a brief comment in the final query explaining how time zone conversion is performed before week bucketing.

Table schema:

transactions(
    user_id INT,
    event_ts TIMESTAMPTZ,   -- stored in UTC
    amount NUMERIC(12,2)
)

Example 1:

Input:
| user_id | event_ts                | amount |
|---------|-------------------------|--------|
| 1       | 2025-09-01 15:00:00+00 | 100.00 |
| 2       | 2025-09-02 06:30:00+00 |  25.00 |
| 1       | 2025-09-08 06:30:00+00 |  15.00 |
| 1       | 2025-09-08 07:30:00+00 |  60.00 |
| 3       | 2025-09-10 10:00:00+00 |  20.00 |
| 4       | 2025-09-22 09:00:00+00 |   5.00 |
| 2       | 2025-09-30 23:00:00+00 |  40.00 |
| 5       | 2025-10-06 06:30:00+00 |  15.00 |
| 6       | 2025-10-13 07:30:00+00 |  10.00 |

Output:
| week_start_date | wk_amount | rolling_3wk_amount |
|-----------------|-----------|--------------------|
| 2025-09-01      | 140.00    | 140.00             |
| 2025-09-08      |  80.00    | 220.00             |
| 2025-09-15      |   0.00    | 220.00             |
| 2025-09-22      |   5.00    |  85.00             |
| 2025-09-29      |  55.00    |  60.00             |
| 2025-10-06      |   0.00    |  60.00             |
| 2025-10-13      |  10.00    |  65.00             |

Explanation: The 2025-09-08 06:30:00+00 row is Sunday evening in Los Angeles and falls into the week starting 2025-09-01, while the 2025-09-08 07:30:00+00 row is Monday morning and starts the next LA-local week.

Example 2:

Input:
| user_id | event_ts                | amount |
|---------|-------------------------|--------|
| 7       | 2025-10-06 06:30:00+00 |  15.00 |
| 8       | 2025-10-06 07:30:00+00 |  50.00 |

Output:
| week_start_date | wk_amount | rolling_3wk_amount |
|-----------------|-----------|--------------------|
| 2025-09-01      |   0.00    |   0.00             |
| 2025-09-08      |   0.00    |   0.00             |
| 2025-09-15      |   0.00    |   0.00             |
| 2025-09-22      |   0.00    |   0.00             |
| 2025-09-29      |  15.00    |  15.00             |
| 2025-10-06      |  50.00    |  65.00             |
| 2025-10-13      |   0.00    |  65.00             |

Explanation: This isolates the midnight boundary: the same UTC date maps to two different LA-local weeks because of the daylight-saving offset.

Constraints:

• event_ts is TIMESTAMPTZ and is stored in UTC.
• amount is NUMERIC(12,2).
• The week range is 2025-09-01 through 2025-10-13, inclusive by week_start_date.
• Use a single SQL statement with CTEs, generate_series, and window functions.
• Do not create temporary tables.
• Include a brief comment in the query explaining time zone conversion before week bucketing.

Schema

CREATE TABLE transactions (
  user_id INT,
  event_ts TIMESTAMPTZ,
  amount NUMERIC(12,2)
);