WAU and Retention Query | Dataford Interview Questions

Given a table of user login events, write a query to calculate the weekly active users (WAU) and the month-over-month retention rate.

Use calendar weeks beginning on Monday. Define monthly retention as the percentage of users active in the previous calendar month who also logged in during the current calendar month. Exclude events with missing users or timestamps.

Output

Return rows for weekly WAU and monthly retention, identified by metric_type.
Include period_start, weekly_active_users, and mom_retention_rate.
Use NULL for the metric that does not apply to each row, and order by period_start.

Schema

login_events

Column	Type	Description
event_idPK	INT	Unique identifier for the login event
user_id	VARCHAR(50)	Identifier of the user who logged in
login_at	TIMESTAMP	Timestamp when the login occurred

Your question is WAU and Retention Query. 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

Given a table of user login events, write a query to calculate the weekly active users (WAU) and the month-over-month retention rate.

Output

Return rows for weekly WAU and monthly retention, identified by metric_type.
Include period_start, weekly_active_users, and mom_retention_rate.
Use NULL for the metric that does not apply to each row, and order by period_start.

Schema

login_events

Column	Type	Description
event_idPK	INT	Unique identifier for the login event
user_id	VARCHAR(50)	Identifier of the user who logged in
login_at	TIMESTAMP	Timestamp when the login occurred

Your question is WAU and Retention Query. 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.