SQL Moving Average | Dataford Interview Questions

How would you use SQL window functions to calculate a moving average of energy consumption over the last 30 days?

Assume each row contains one meter's daily reading. Calculate total available consumption per day across meters, then calculate the average of daily totals within the inclusive 30-day period ending on each date.

Output

One row per reading date with reading_date, daily_consumption_kwh, and moving_avg_30d_kwh.
Include all dates represented in the data and order by reading_date ascending.

Schema

energy_consumption_readings

Column	Type	Description
reading_idPK	INT	Unique identifier for the consumption reading
meter_id	VARCHAR(30)	Identifier of the electric meter
reading_date	DATE	Calendar date of the reading
consumption_kwh	NUMERIC(12,2)	Energy consumed by the meter in kilowatt-hours

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

How would you use SQL window functions to calculate a moving average of energy consumption over the last 30 days?

Output

One row per reading date with reading_date, daily_consumption_kwh, and moving_avg_30d_kwh.
Include all dates represented in the data and order by reading_date ascending.

Schema

energy_consumption_readings

Column	Type	Description
reading_idPK	INT	Unique identifier for the consumption reading
meter_id	VARCHAR(30)	Identifier of the electric meter
reading_date	DATE	Calendar date of the reading
consumption_kwh	NUMERIC(12,2)	Energy consumed by the meter in kilowatt-hours

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