Write rolling-window SQL over weekly cohorts

You have two tables: users and transactions. Write a single SQL query (use CTEs) to produce, for each calendar week and user, weekly_revenue, a 4-week rolling sum of revenue, week-over-week percent change, and the rank of each user within the week by rolling revenue (ties allowed). Use date_trunc('week', ts) for week starts (weeks start Monday, timestamps are UTC). Additionally, return first_purchase_date for each user and the number of days between first and second purchase. Return columns: week_start, user_id, weekly_revenue, rolling_4w_revenue, wow_change_pct, rank_in_week, first_purchase_date, days_to_second_purchase. Requirements: (a) Use SUM(...) OVER with a ROWS-based rolling window equivalent to 4 weeks; (b) Use LAG to compute week-over-week change and filter to rows where weekly_revenue decreased by at least 20% vs prior week; (c) Use DENSE_RANK to rank users by rolling_4w_revenue per week and output only the top 3 ranks per week; (d) Ensure users with fewer than 2 purchases still appear with NULL days_to_second_purchase. Schema and small sample data:

users
user_id | signup_date | country
1 | 2025-06-02 | US
2 | 2025-06-15 | CA
3 | 2025-07-01 | US
4 | 2025-07-20 | UK

transactions
tx_id | user_id | ts | amount
t1 | 1 | 2025-07-07 10:00:00 | 50
t2 | 1 | 2025-07-12 09:00:00 | 30
t3 | 1 | 2025-07-21 14:00:00 | 20
t4 | 2 | 2025-07-09 12:00:00 | 40
t5 | 2 | 2025-07-28 08:00:00 | 60
t6 | 3 | 2025-07-15 11:00:00 | 70
t7 | 3 | 2025-07-22 17:00:00 | 20
t8 | 3 | 2025-08-05 10:00:00 | 50
t9 | 1 | 2025-08-10 10:10:00 | 90
t10 | 2 | 2025-08-16 13:00:00 | 30

Notes: Treat missing prior-week revenue as NULL when computing wow_change_pct; assume a transaction implies a purchase.

Weekly user revenue with 4-week rolling window and rankings

You are given two tables, users and transactions. Write a single SQL query (you may use multiple CTEs) that produces, for each calendar week and user, the following metrics:

• weekly_revenue: total revenue for that user in that calendar week
• rolling_4w_revenue: a 4-week rolling sum of weekly_revenue for that user, using a ROWS-based window
• wow_change_pct: week-over-week percent change in weekly_revenue vs the prior (previous-row) week for that user
• rank_in_week: the DENSE_RANK of each user within the same week, ordered by rolling_4w_revenue (ties allowed)
• first_purchase_date: date of the user's first purchase (based on transactions.ts)
• days_to_second_purchase: number of days between the first and second purchase; NULL if the user has fewer than 2 purchases

Use date_trunc('week', ts) as the week start (weeks start on Monday; timestamps are UTC). Treat a missing prior-week revenue as NULL when computing wow_change_pct.

Return the following columns:

• week_start (timestamp, from date_trunc('week', ts))
• user_id
• weekly_revenue
• rolling_4w_revenue
• wow_change_pct
• rank_in_week
• first_purchase_date
• days_to_second_purchase

Additional requirements: (a) Use SUM(...) OVER with a ROWS-based rolling window equivalent to 4 weeks (i.e., over the current row and the previous 3 weekly rows per user). (b) Use LAG to compute week-over-week change and filter the final result to only rows where weekly_revenue decreased by at least 20% versus the prior week (wow_change_pct <= -0.20). (c) Use DENSE_RANK to rank users by rolling_4w_revenue per week and output only rows where rank_in_week is in the top 3 ranks for that week. (d) Ensure users with fewer than 2 purchases still appear in the output (for weeks where they have revenue) with days_to_second_purchase = NULL.

Write the query for a PostgreSQL-like SQL dialect. Format week_start as YYYY-MM-DD HH24:MI:SS in the final output.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL
country	VARCHAR(3)	NOT NULL

Sample data:

country	signup_date	user_id
US	2025-06-02	1
CA	2025-06-15	2
US	2025-07-01	3
UK	2025-07-20	4

Table transactions

column	type	constraints
tx_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
ts	TIMESTAMP	NOT NULL
amount	DECIMAL(10,2)	NOT NULL

Sample data:

amount	ts	tx_id	user_id
50	2025-07-07 10:00:00	t1	1
30	2025-07-12 09:00:00	t2	1
20	2025-07-21 14:00:00	t3	1
40	2025-07-09 12:00:00	t4	2
60	2025-07-28 08:00:00	t5	2
70	2025-07-15 11:00:00	t6	3
20	2025-07-22 17:00:00	t7	3
50	2025-08-05 10:00:00	t8	3
90	2025-08-10 10:10:00	t9	1
30	2025-08-16 13:00:00	t10	2

Expected output

One row per user-week where (1) the user’s weekly_revenue decreased by at least 20% versus that user’s prior week with revenue, and (2) the user’s rolling_4w_revenue is ranked in the top 3 DENSE_RANKs for that week across all users. Includes first_purchase_date and days_to_second_purchase for each user.

days_to_second_purchase	first_purchase_date	rank_in_week	rolling_4w_revenue	user_id	week_start	weekly_revenue	wow_change_pct
5	2025-07-07	1	100.00	1	2025-07-21 00:00:00	20.00	-0.75
7	2025-07-15	2	90.00	3	2025-07-21 00:00:00	20.00	-0.7142857143
19	2025-07-09	1	130.00	2	2025-08-11 00:00:00	30.00	-0.5

Hints

1. First aggregate to a per-user, per-week level using date_trunc('week', ts) before applying window functions.
2. Use window functions: SUM(...) OVER (ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) for the rolling 4-week sum, LAG for week-over-week change, DENSE_RANK for weekly ranking, and a separate CTE with ROW_NUMBER to derive first and second purchase dates per user.