7-Day Rolling Engagement With Cohorts | Dataford Interview Questions

Write a SQL query using window functions to compute each user's 7-day rolling engagement and rank users within each cohort.

Use the provided users and engagement_events tables. Treat a NULL engagement score as zero, include only user-date combinations with at least one event, and calculate ranks separately for each cohort and activity date.

Output

One row per active user and activity date.
Columns: cohort_name, activity_date, user_id, user_name, rolling_engagement, and cohort_rank.
rolling_engagement covers the activity date and the six preceding calendar days.
Order by cohort_name with NULL values last, activity_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_name	VARCHAR(50)	User cohort label

engagement_events

Column	Type	Description
event_idPK	INT	Unique engagement event identifier
user_id	INT	User associated with the event
event_date	DATE	Calendar date of the event
event_type	VARCHAR(50)	Type of engagement event
engagement_points	INT	Engagement value assigned to the event

Your question is 7-Day Rolling Engagement With Cohorts. 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