Monthly User Engagement Trend Analysis | Dataford Interview Questions

Your question is Monthly User Engagement Trend Analysis. Start with the requirements and the three 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

StreamWave wants to analyze how user behavior changes over time. Write a PostgreSQL query to summarize monthly user activity for each signup cohort month.

Your query should focus on users who signed up in January or February 2024 and measure their behavior during the first 3 calendar months starting from their signup month.

Requirements

Return one row per signup_month and activity_month.
Count:
- total users in the cohort
- active users in that month (users with at least one event)
- total events in that month
Add an engagement_status column:
- high if average events per active user is at least 2
- low otherwise
Order results by signup_month, then activity_month.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user registered
acquisition_channel	VARCHAR(50)	Marketing acquisition source

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_date	DATE	Date of the event
event_type	VARCHAR(50)	Type of user action
device_type	VARCHAR(20)	Device used for the event

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription identifier
user_id	INT	User tied to the subscription
plan_name	VARCHAR(30)	Subscription plan name
start_date	DATE	Subscription start date
end_date	DATE	Subscription end date if canceled