SQL for Monthly Active Users | Dataford Interview Questions

Your question is SQL for Monthly Active Users. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Glassdoor wants to monitor engagement across its Jobs, Reviews, and Community experiences. Write a PostgreSQL query to calculate monthly active users, where an active user is an active Glassdoor member who generated at least one qualifying activity event during that calendar month.

Requirements

Include only members whose account_status is active.
Treat page_view, job_search, application, and review_view as qualifying events.
Count each member once per calendar month, even when they generate multiple events.
Return the month as YYYY-MM, the monthly active user count, and sort chronologically.

Schema

members

Column	Type	Description
member_idPK	INTEGER	Unique Glassdoor member identifier
account_status	VARCHAR(20)	Current member account status

activity_events

Column	Type	Description
event_idPK	INTEGER	Unique activity event identifier
member_id	INTEGER	Member associated with the event
occurred_at	TIMESTAMP	Timestamp when the activity occurred
event_type	VARCHAR(30)	Type of activity generated by the member
surface	VARCHAR(30)	Glassdoor product surface where the event occurred