7-Day Rolling Active Users | Dataford Interview Questions

Welcome to the SQL screen.

The question is on your right: 7-Day Rolling Active Users. Read through the requirements and the one table first.

Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?

Problem

StreamWave tracks user activity across its app and wants a daily engagement trend. Write a SQL query to calculate the 7-day rolling average of daily active users.

A user is considered active on a given date if they generated at least one event that day. Use the event log and return one row per calendar date present in the data.

Requirements

Count distinct active users per day from the user_events table.
Calculate the 7-day rolling average of daily active users, ordered by event date.
Include dates even when user_id is NULL; those rows should not count toward active users.
Return the columns event_date, daily_active_users, and rolling_7_day_avg.
Order the final result by event_date ascending.

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique event record ID
user_id	INT	User who generated the event; may be NULL for anonymous events
event_name	VARCHAR(50)	Type of user event
event_time	TIMESTAMP	Timestamp when the event occurred