Pre/Post Launch Cohort Comparison | Dataford Interview Questions

Given a table of user events, write a SQL query to compare pre- and post-launch metrics by cohort.

Use 2025-01-15 as the launch timestamp, with 30-day pre-launch and post-launch windows. Define cohorts by the month of each user's signup date. Metrics are total events and distinct active users.

Output

One row per signup cohort, including cohorts with no events in either window.
Columns: cohort_month, pre_event_count, post_event_count, pre_active_users, post_active_users, and event_count_pct_change.
Calculate percentage change from pre-launch to post-launch, return NULL when the pre-launch count is zero, and order by cohort_month ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up

user_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User associated with the event
event_time	TIMESTAMP	Timestamp when the event occurred
event_name	VARCHAR(50)	Name of the recorded event

Your question is Pre/Post Launch Cohort Comparison. 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