SQL Window Functions for Rolling Metrics

How would you use SQL window functions to calculate a 7-day rolling average and rank users within each cohort?

Write a PostgreSQL query using the provided user and activity data. Calculate each active user's rolling average through their latest activity date, then rank users within the same cohort. Users without activity should be excluded.

Output

1. One row per active user, with user_id, cohort_date, latest_activity_date, rolling_7_day_average, and cohort_rank.
2. Tied rolling averages share a rank, and subsequent ranks should be skipped.
3. Order by cohort_date, cohort_rank, and user_id.

Output details

• Average each user's daily activity totals over the inclusive calendar interval from the activity date through the preceding six days, omitting dates without activity.
• Treat NULL activity values as ignored by averaging, but return NULL when no non-NULL value exists; preserve zero values as zero.
• Preserve full PostgreSQL numeric precision for rolling_7_day_average; do not round or cast it.
• Rank using exact unrounded rolling-average values, including NULL values according to PostgreSQL's default descending sort behavior.