Rolling Averages With Window Functions | Dataford Interview Questions

Zeta's Data Analyst team reviews campaign revenue trends in Zeta Campaign Manager. Write a PostgreSQL query that calculates a rolling average of reported daily revenue for active campaigns during the first five days of January 2025.

Requirements

Join campaigns to their daily metrics and include only campaigns with status = 'active' and metrics from January 1 through January 5, 2025.
Aggregate multiple metric rows for the same campaign and date into one daily_revenue value. Treat a day containing only NULL revenue values as zero.
Calculate a three-observation rolling average using the current observation and up to the two preceding reported observations within each campaign. Round the result to two decimal places.
Return results ordered by campaign name and metric date.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Campaign identifier
campaign_name	VARCHAR(100)	Campaign name
status	VARCHAR(20)	Current campaign status

campaign_daily_metrics

Column	Type	Description
metric_idPK	INT	Metric row identifier
campaign_id	INT	References campaigns.campaign_id
metric_date	DATE	Date on which the metric was recorded
revenue	DECIMAL(10,2)	Revenue reported by the metric row

Your question is Rolling Averages With Window Functions. 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