SQL for DAU and MAU | Dataford Interview Questions

Given a table of PlayStation Network login timestamps, write a SQL query to calculate the daily active users (DAU) and monthly active users (MAU) for the past year.

Treat MAU as the number of distinct users active during the calendar month containing each activity date. Exclude invalid timestamps and do not count NULL user IDs.

Output

One row per date with at least one login event in the past year.
Columns: activity_date, daily_active_users, and monthly_active_users.
Order by activity_date ascending.

Schema

psn_logins

Column	Type	Description
login_idPK	BIGINT	Unique identifier for the login event
user_id	BIGINT	PlayStation Network user identifier
login_timestamp	TIMESTAMP	Timestamp when the login occurred

Your question is SQL for DAU and MAU. 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