SQL Rolling Averages and Anomalies | Dataford Interview Questions

S&P Global teams monitor product-level financial metrics across platforms such as Capital IQ and Market Intelligence. Write a PostgreSQL query that calculates a trailing three-observation average and flags unusually large fluctuations.

Requirements

Join financial_metrics to spg_products and include only active products with non-null metric values.
Calculate the average of the three preceding observations for each product, ordered by metric_date.
Flag a row as anomalous when its metric differs from the trailing average by more than 20 percent. Rows without three preceding observations are not anomalous.
Return the product name, date, metric value, rounded trailing average, and anomaly flag in chronological order per product.

Schema

spg_products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	S&P Global product name
is_active	BOOLEAN	Whether the product is monitored

financial_metrics

Column	Type	Description
metric_idPK	INT	Unique metric observation identifier
product_id	INT	Referenced product identifier
metric_date	DATE	Date of the observation
metric_value	DECIMAL(14,2)	Observed financial metric value

Your question is SQL Rolling Averages and Anomalies. 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