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.