Rolling Metrics with Window Functions | Dataford Interview Questions

Your question is Rolling Metrics with Window Functions. 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

Tesla Energy wants to monitor Supercharger usage by vehicle. Write a PostgreSQL query that calculates rolling energy metrics for completed charging sessions associated with known Tesla vehicles.

Requirements

Exclude cancelled sessions and sessions with missing energy measurements.
Calculate each vehicle's rolling average energy and rolling total energy across the current session and two preceding completed sessions, ordered chronologically.
Return the vehicle identifier, model, session timestamp, session energy, rolling average, and rolling total. Sort by vehicle and session time.

Schema

vehicles

Column	Type	Description
vehicle_idPK	INT	Unique Tesla vehicle identifier
vin	VARCHAR(17)	Vehicle identification number
model	VARCHAR(40)	Tesla vehicle model
delivery_region	VARCHAR(30)	Vehicle delivery region

supercharger_sessions

Column	Type	Description
session_idPK	INT	Unique charging session identifier
vehicle_id	INT	Vehicle associated with the session
session_start	TIMESTAMP	Session start timestamp
energy_kwh	DECIMAL(8,2)	Energy delivered in kilowatt-hours
session_status	VARCHAR(20)	Charging session status