SQL Window Functions | Dataford Interview Questions

Your question is SQL Window Functions. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Adastra Group analysts monitor campaign performance across its data and analytics engagements. Write a PostgreSQL query to calculate a three-day moving average of daily campaign spend for active campaigns.

Requirements

Include only campaigns with status = 'active' and metrics recorded from 2024-01-01 through 2024-01-05.
Join campaign metadata to daily metrics and return one row per campaign and metric date.
Calculate the moving average using the current row and up to the two preceding rows within each campaign, ordered by metric_date.
Round the moving average to two decimal places and order the final output by campaign ID and date.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign display name
channel	VARCHAR(50)	Campaign marketing channel
status	VARCHAR(20)	Campaign lifecycle status

campaign_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric identifier
campaign_id	INT	References campaigns.campaign_id
metric_date	DATE	Date of the daily measurement
daily_spend	DECIMAL(10,2)	Campaign spend recorded for the day