Rolling 30-Day User Average | Dataford Interview Questions

Business Context

Bidgely uses interval energy data to monitor household consumption patterns. A rolling average helps smooth daily variation and supports customer insights in the Bidgely EnergyAI platform.

Task

Write a PostgreSQL query to calculate the rolling average daily energy consumption for each user over a 30-day period.

Requirements

Aggregate multiple readings for the same user and calendar date into one daily average.
Calculate a rolling average using the current date and the preceding 29 calendar days, partitioned by user.
Include users with valid readings, preserve NULL consumption behavior, and order the results by user and date.
Round daily and rolling averages to two decimal places.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Bidgely user identifier
user_name	VARCHAR(100)	User display name

energy_readings

Column	Type	Description
reading_idPK	INTEGER	Unique energy reading identifier
user_id	INTEGER	Referenced Bidgely user identifier
reading_date	DATE	Date of the energy measurement
consumption_kwh	NUMERIC(10,2)	Energy consumption in kilowatt-hours

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