SQL Rolling Engagement Metrics | Dataford Interview Questions

How would you write a SQL query to extract user engagement metrics over a rolling 30-day window?

Use the users and engagement_events tables. Calculate metrics for each user and event date, using the current date and the preceding 29 calendar days. Include only users with at least one recorded event.

Output

One row per user and event date
Columns: user_id, display_name, event_date, daily_event_count, rolling_event_count, and rolling_active_days
Include events from known users only, and count the current date within each 30-day window
Order by user_id, then event_date ascending

Schema

users

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

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 when the event occurred
event_type	VARCHAR(50)	Type of engagement event

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