SQL Window Functions for Rolling Metrics | Dataford Interview Questions

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

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

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
cohort_date	DATE	Date the user entered the cohort

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	User associated with the activity
activity_date	DATE	Calendar date of activity
activity_count	INT	Number of activities recorded that day

Your question is SQL Window Functions for Rolling Metrics. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem