7-Day Rolling Retention SQL

Write a SQL query that computes 7-day rolling user retention from a daily events table, then flags cohorts whose week-over-week retention dropped more than 10%.

Treat each user's cohort as the calendar week containing their first event. Retention for week n is the percentage of cohort users active at least once during the corresponding 7-day interval. Include partially observed final intervals through the latest event date.

Output

1. One row per cohort week and retention week.
2. Columns: cohort_week, week_number, cohort_users, retained_users, retention_rate, prior_retention_rate, and dropped_more_than_10pct.
3. Include prior retention when available, flag relative declines greater than 10%, and order by cohort_week, then week_number.