Rolling Average Energy SQL | Dataford Interview Questions

Write a SQL query to calculate the rolling average of energy consumption for a set of buildings over a seven-day window.

Use the provided building and energy-consumption data. Aggregate multiple readings for the same building and date before calculating the rolling average. The window includes the current date and the preceding six calendar days.

Output

One row per building and recorded consumption date.
Columns: building_name, reading_date, daily_consumption, and rolling_7_day_avg.
Include only dates with recorded consumption, order by building_id, then reading_date.

Schema

buildings

Column	Type	Description
building_idPK	INT	Unique building identifier
building_name	VARCHAR(100)	Building name
region	VARCHAR(50)	Geographic operating region

energy_consumption

Column	Type	Description
reading_idPK	INT	Unique energy reading identifier
building_id	INT	Building associated with the reading
reading_date	DATE	Date of energy consumption
energy_kwh	NUMERIC(10,2)	Energy consumed in kilowatt-hours

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