SQL Rolling 7-Day Vehicle Averages | Dataford Interview Questions

Your question is SQL Rolling 7-Day Vehicle Averages. 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

Rivian's telemetry analytics team wants to monitor vehicle efficiency metrics across its connected fleet. Write a PostgreSQL query that calculates a rolling 7-day average of energy_consumption_kwh for each Rivian vehicle.

Requirements

Consider only telemetry events where event_type = 'drive' and the vehicle exists in rivian_vehicles.
Aggregate multiple telemetry events for the same vehicle and calendar date into one daily average. Ignore NULL metric values.
Use a window function to calculate the average of available daily averages from the current date and the preceding six calendar days.
Return the vehicle, event date, daily average, and rolling average, ordered by vehicle and date. Round averages to two decimal places.

Schema

rivian_vehicles

Column	Type	Description
vehicle_idPK	INT	Unique vehicle identifier
model	VARCHAR(50)	Rivian vehicle model
delivery_date	DATE	Date the vehicle was delivered

vehicle_telemetry_events

Column	Type	Description
event_idPK	INT	Unique telemetry event identifier
vehicle_id	INT	Vehicle associated with the event
recorded_at	TIMESTAMP	Timestamp when the event was recorded
event_type	VARCHAR(20)	Telemetry context, such as drive or charge
energy_consumption_kwh	NUMERIC(8,2)	Energy consumed during the event