SQL for Running Average | Dataford Interview Questions

EXL Service uses an operations dashboard to monitor daily service performance. Write a PostgreSQL query that calculates a rolling average for the current date and the previous four calendar days.

Requirements

First calculate the average metric value for each date because multiple observations can occur on the same date.
Use the calendar table so dates without observations remain in the result.
Calculate the five-day rolling average with a window function ordered by calendar date. Ignore missing or NULL daily averages when calculating the average.
Round both the daily average and rolling average to two decimal places, and return results in chronological order.

Schema

exl_service_calendar

Column	Type	Description
calendar_datePK	DATE	Calendar date used to preserve dates without observations

service_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique service metric observation identifier
metric_date	DATE	Date on which the observation was recorded
metric_name	VARCHAR(50)	Name of the monitored service metric
metric_value	DECIMAL(10,2)	Observed metric value

Your question is SQL for Running Average. 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