Rolling 7-Day Audience Average | Dataford Interview Questions

The Nielsen needs a daily audience trend for Nielsen measurement regions. Write a PostgreSQL query that calculates a calendar-day rolling average for each region.

Requirements

Aggregate multiple audience observations for the same region and date.
Generate every date in the requested period, treating missing or NULL audience counts as zero.
Calculate a seven-calendar-day average, including the current date and six preceding dates within each region.
Return the region, date, daily audience, and rolling average, rounded to two decimals, ordered by region and date.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique region identifier
region_name	VARCHAR(80)	Nielsen measurement region name

audience_observations

Column	Type	Description
observation_idPK	INT	Unique audience observation identifier
region_id	INT	References regions.region_id
audience_date	DATE	Date of the audience measurement
audience_count	INT	Observed audience count

Your question is Rolling 7-Day Audience 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