SQL Rolling Average with Windows | Dataford Interview Questions

Business context

Harnham wants to monitor daily applications to its Recruiter Cloud platform and identify whether recent activity is improving compared with the previous week.

Task

Write a PostgreSQL query that joins the daily metric records to the product catalog, calculates a seven-day rolling average of applications, and compares each available day with the rolling average from seven days earlier.

Requirements

Filter to the Harnham Recruiter Cloud product and the applications metric.
Calculate a seven-calendar-day rolling average using the metric date, ignoring NULL metric values through PostgreSQL aggregate behavior.
Use a window function to retrieve the rolling average from seven days earlier.
Return results from 2025-01-08 onward, ordered by metric date, with the percentage change versus the prior week.

Schema

product_catalog

Column	Type	Description
product_idPK	INTEGER	Unique product identifier
product_name	VARCHAR(100)	Harnham product name

daily_metrics

Column	Type	Description
metric_idPK	INTEGER	Unique metric record identifier
product_id	INTEGER	References product_catalog.product_id
metric_name	VARCHAR(50)	Name of the measured metric
metric_date	DATE	Calendar date of the measurement
metric_value	NUMERIC(12,2)	Metric value, which may be unavailable

Your question is SQL Rolling Average with Windows. 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