Rolling Cohort User Average | Dataford Interview Questions

Write a SQL query to find the rolling 7-day average of active users, partitioned by specific clinical cohorts at nference.

Use the provided cohort, user, and activity data. Include calendar dates with no activity so inactive days contribute zero to the average.

Output

One row per clinical cohort and calendar date in the activity range
Columns: cohort_id, cohort_name, activity_date, daily_active_users, and rolling_7_day_avg
Include all cohorts represented by users, order by cohort_id, then activity_date

clinical_cohorts

Column	Type	Description
cohort_idPK	VARCHAR(20)	Unique clinical cohort identifier
cohort_name	VARCHAR(100)	Clinical cohort name
clinical_domain	VARCHAR(100)	Clinical domain associated with the cohort

users

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
user_id	VARCHAR(20)	User associated with the activity event
activity_date	DATE	Calendar date of activity
activity_type	VARCHAR(40)	Type of activity recorded

Your question is Rolling Cohort User Average. Start with the requirements and the three 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.