Monthly Active Users Query | Dataford Interview Questions

Your question is Monthly Active Users Query. 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

TransUnion CreditVision needs a monthly engagement metric showing how many eligible users interacted with the product. The source contains both user account status and timestamped product events.

Write a PostgreSQL query to calculate monthly active users from the events table.

Requirements

Count each eligible user once per calendar month, even if the user generated multiple events.
Include only users whose account_status is active.
Treat login, report_view, and score_check as active events. Exclude other event types and rows with a NULL event name.
Return one row per month, ordered chronologically, with the month start date and active user count.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
account_status	VARCHAR(20)	Current account status

events

Column	Type	Description
event_idPK	INTEGER	Unique event identifier
user_id	INTEGER	User who generated the event
occurred_at	TIMESTAMP	Timestamp when the event occurred
event_name	VARCHAR(30)	Type of product event