Rolling 7-Day Average Per Smart Meter | Dataford Interview Questions

Write a query to calculate the rolling 7-day average of energy consumption per smart meter for EDF.

Use the meter_readings table. Treat each row as a meter reading for a date, retain dates whose consumption is NULL, and calculate averages from available non-NULL daily values.

Output

One row per smart meter and reading date
Columns: meter_id, reading_date, daily_consumption, and rolling_7_day_avg
Include the current date and the six preceding calendar days in each rolling period
Order by meter_id, then reading_date

Schema

meter_readings

Column	Type	Description
reading_idPK	INT	Unique identifier for the meter reading
meter_id	VARCHAR(20)	Smart meter identifier
reading_date	DATE	Date of the energy reading
consumption_kwh	NUMERIC(10,2)	Energy consumption in kilowatt-hours

Your question is Rolling 7-Day Average Per Smart Meter. Start with the requirements and the one table 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