Rolling Engagement Metrics SQL | Dataford Interview Questions

Write a complex SQL query using window functions (e.g., DENSE_RANK, LAG, LEAD) to calculate 7-day rolling user engagement metrics across multiple product categories at Apple. Use the users, product_categories, and engagement_events tables. Include completed events from January 1 through January 10, 2025, treating a NULL engagement score as zero.

Output

One row per user, category, and activity date.
Return user_id, category_name, activity_date, daily_engagement, rolling_7d_engagement, previous_rolling_7d, next_rolling_7d, and category_rank.
Rank rows by rolling engagement within each activity date, with ties sharing a rank. Order by activity date, rank, user ID, and category name.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Apple user identifier
user_name	VARCHAR(100)	User display name

product_categories

Column	Type	Description
category_idPK	INT	Unique product category identifier
category_name	VARCHAR(100)	Apple product category name

engagement_events

Column	Type	Description
event_idPK	INT	Unique engagement event identifier
user_id	INT	User who generated the event
category_id	INT	Product category associated with the event
event_date	DATE	Calendar date of the event
engagement_score	INT	Engagement points assigned to the event
event_type	VARCHAR(40)	Type of engagement event
event_status	VARCHAR(20)	Processing status of the event

Your question is Rolling Engagement Metrics SQL. 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.

Problem