Pre vs Post Metrics by Cohort | Dataford Interview Questions

Write a SQL query to compare pre- and post-launch metrics by cohort using CTEs and joins.

Use the provided cohort assignments and metric observations. Treat observations from the 30 days before launch as pre-launch and observations from launch through the following 29 days as post-launch.

Output

One row per valid cohort, with cohort_month, pre_avg_metric, post_avg_metric, absolute_change, and percent_change.
Include cohorts without post-launch observations, using NULL for unavailable comparison values.
Round metric averages, absolute changes, and percentage changes to two decimal places. Order by cohort_month ascending.

Schema

cohort_assignments

Column	Type	Description
user_idPK	INT	Unique user identifier
cohort_month	DATE	Month assigned to the user cohort
launch_date	DATE	Launch date applicable to the user's cohort

user_metrics

Column	Type	Description
metric_idPK	INT	Unique metric observation identifier
user_id	INT	User associated with the observation
metric_date	DATE	Date of the metric observation
metric_value	DECIMAL(10,2)	Observed metric value

Your question is Pre vs Post Metrics by Cohort. 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