SQL Month-over-Month Metric Changes | Dataford Interview Questions

Write a SQL query to compare metrics across time periods and highlight changes month over month.

Use the provided metric definitions and observations. Include metrics with no prior month for comparison, and avoid calculating a percentage change when the prior value is missing or zero.

Output

One row per metric and observed month.
Columns: metric_name, month, metric_value, prior_month_value, change_value, and change_percent.
Include all observed metric months, order by metric_name and month ascending, and return NULL for unavailable comparisons.

Schema

metric_definitions

Column	Type	Description
metric_idPK	INT	Unique identifier for the metric
metric_name	VARCHAR(100)	Display name of the metric
unit	VARCHAR(30)	Unit used to interpret the metric value

metric_observations

Column	Type	Description
observation_idPK	INT	Unique observation identifier
metric_id	INT	Metric identifier associated with the observation
observed_at	DATE	Date on which the metric was observed
metric_value	DECIMAL(12,2)	Observed metric value

Your question is SQL Month-over-Month Metric Changes. 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