Rolling Driver Metrics in SQL | Dataford Interview Questions

Your question is Rolling Driver Metrics in SQL. 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

Geotab fleet analysts use vehicle trip data to monitor changes in driver travel patterns. Write a PostgreSQL query that calculates each driver's 7-day rolling average trip distance.

Requirements

Aggregate trip distance by driver and calendar date before applying the window function.
Build a complete daily date series for the available reporting period so days without trips count as zero distance.
Calculate a seven-calendar-day rolling average for each driver, including the current date and six preceding dates.
Return results only for dates on which the driver has at least one recorded trip.
Treat a NULL trip distance as zero, round the rolling average to two decimal places, and order by driver and date.

Schema

drivers

Column	Type	Description
driver_idPK	INT	Unique driver identifier
driver_name	VARCHAR(100)	Driver's name
region	VARCHAR(50)	Fleet operating region

trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
driver_id	INT	References drivers.driver_id
trip_date	DATE	Calendar date of the trip
distance_km	NUMERIC(10,2)	Trip distance in kilometers