Write a SQL query using window functions to calculate a rolling 7-day average and rank users within each cohort.
