Daily User Engagement Metrics | Dataford Interview Questions

StreamLine wants a simple daily engagement report from its app event log. Write a SQL query to calculate user engagement metrics for a specific date range using only the user_events table.

Requirements

Return one row per event_date for dates between 2024-03-01 and 2024-03-05 inclusive.
For each date, calculate:
- total_events: total number of event rows
- active_users: count of distinct non-null user_id values
Sort the result by event_date ascending.

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique identifier for each event
user_id	INT	User who triggered the event; NULL for anonymous activity
event_type	VARCHAR(50)	Type of user event
event_date	DATE	Calendar date when the event occurred
device_type	VARCHAR(20)	Device used for the event

Your question is Daily User Engagement Metrics. Start with the requirements and the one table 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

StreamLine wants a simple daily engagement report from its app event log. Write a SQL query to calculate user engagement metrics for a specific date range using only the user_events table.

Requirements

Return one row per event_date for dates between 2024-03-01 and 2024-03-05 inclusive.
For each date, calculate:
- total_events: total number of event rows
- active_users: count of distinct non-null user_id values
Sort the result by event_date ascending.

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique identifier for each event
user_id	INT	User who triggered the event; NULL for anonymous activity
event_type	VARCHAR(50)	Type of user event
event_date	DATE	Calendar date when the event occurred
device_type	VARCHAR(20)	Device used for the event

Your question is Daily User Engagement Metrics. Start with the requirements and the one table 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.