MAU and Retention for Roku | Dataford Interview Questions

Write a SQL query to calculate the monthly active users (MAU) and the month-over-month retention rate for Roku streaming devices.

Use only valid playback activity from Roku devices associated with known users. Treat a user as active once per calendar month.

Output

One row per month with activity, ordered chronologically.
Columns: month_start, mau, previous_month_mau, retained_users, and retention_rate_pct.
Retention is the percentage of the previous month's users who were also active in the current month. Return NULL when no previous-month denominator exists.

users

Column	Type	Description
user_idPK	INT	Unique Roku account identifier
user_name	VARCHAR(100)	Display name for the Roku account
account_status	VARCHAR(20)	Current account status

roku_devices

Column	Type	Description
device_idPK	INT	Unique Roku streaming device identifier
user_id	INT	Roku account associated with the device
model_name	VARCHAR(80)	Roku device model

device_activity

Column	Type	Description
activity_idPK	INT	Unique activity event identifier
device_id	INT	Device that generated the event
activity_at	TIMESTAMP	Timestamp when the activity occurred
event_type	VARCHAR(40)	Type of device activity event

Your question is MAU and Retention for Roku. 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.