Rolling 7-Day Energy Average | Dataford Interview Questions

Your question is Rolling 7-Day Energy Average. 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

Omaha Public Power District (OPPD) needs a customer-level view of recent electricity consumption. Write a PostgreSQL query that first consolidates readings recorded on the same date, then calculates each customer's rolling calendar-based 7-day average usage.

Requirements

Join customers to energy_usage and return only usage associated with a known customer.
Aggregate multiple readings for the same customer and date into one daily usage value using AVG.
Use a window function partitioned by customer and ordered by date. The rolling window must include the current date and the six preceding calendar days, excluding older dates even when some dates have no readings.
Return results ordered by customer ID and usage date, with the rolling average rounded to two decimal places. NULL readings should be ignored by PostgreSQL's aggregate functions.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique OPPD customer identifier
customer_name	VARCHAR(100)	Customer name

energy_usage

Column	Type	Description
usage_idPK	INTEGER	Unique usage reading identifier
customer_id	INTEGER	References customers.customer_id
usage_date	DATE	Date of the usage reading
kwh_used	NUMERIC(10,2)	Energy consumed in kilowatt-hours