Compute DAU and Retention | Dataford Interview Questions

Given a table of user logins on DigitalOcean, write a query to find the daily active users (DAU) and month-over-month retention rates.

Use the previous calendar month as the comparison period. Exclude login records without a user ID.

Output

One row per date with at least one valid login.
Columns: activity_date, daily_active_users, and month_over_month_retention_rate.
Repeat the monthly retention rate for each date in that month, use NULL when no prior month exists, and order by activity_date ascending.

user_logins

Column	Type	Description
login_idPK	INT	Unique login event identifier
user_id	INT	DigitalOcean user identifier
login_date	TIMESTAMP	Timestamp when the user logged in

Your question is Compute DAU and Retention. 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.

Given a table of user logins on DigitalOcean, write a query to find the daily active users (DAU) and month-over-month retention rates.

Use the previous calendar month as the comparison period. Exclude login records without a user ID.

One row per date with at least one valid login.
Columns: activity_date, daily_active_users, and month_over_month_retention_rate.
Repeat the monthly retention rate for each date in that month, use NULL when no prior month exists, and order by activity_date ascending.

user_logins

Column	Type	Description
login_idPK	INT	Unique login event identifier
user_id	INT	DigitalOcean user identifier
login_date	TIMESTAMP	Timestamp when the user logged in

Your question is Compute DAU and Retention. 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.