Write Rolling Average SQL | Dataford Interview Questions

Engie Impact uses customer energy data to monitor consumption patterns and support operational planning. Write a PostgreSQL query that calculates a 7-day rolling average of daily energy usage for each customer.

The rolling average should average recorded daily totals within the current date and the preceding six calendar days. Days without readings are not included as zero-usage days.

Requirements

Aggregate multiple readings for the same customer and date into one daily usage total.
Calculate the rolling average separately for each customer using a window function and a six-day preceding date range.
Include the customer ID, customer name, usage date, daily usage in kWh, and rolling average in kWh.
Exclude readings with no matching customer, while allowing NULL usage values to be ignored by the aggregate functions.
Order the results by customer ID and usage date.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer or account name

energy_usage

Column	Type	Description
reading_idPK	INT	Unique usage reading identifier
customer_id	INT	Customer associated with the reading
usage_date	DATE	Date of energy usage
usage_kwh	NUMERIC(12,2)	Energy consumed in kilowatt-hours

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