Monthly User Counts and Growth (SQL) | Dataford Interview Questions

Write a SQL query to compare monthly user counts and growth rates.

Use the user activity records to calculate distinct active users by calendar month. Include the previous month's count and the percentage change from the previous month.

Output

One row per calendar month with activity
Columns: month, monthly_user_count, previous_month_count, and growth_rate_pct
Return months chronologically. The first month's previous count and growth rate should be NULL.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address
created_at	TIMESTAMP	User registration timestamp

user_activities

Column	Type	Description
activity_idPK	INT	Unique activity identifier
user_id	INT	User associated with the activity
activity_at	TIMESTAMP	Timestamp when the activity occurred
activity_type	VARCHAR(50)	Type of user activity

Your question is Monthly User Counts and Growth (SQL). Start with the requirements and the two 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.

Problem