Total Driver Time Per Day | Dataford Interview Questions

Uber's driver activity reporting needs daily totals even when an Uber trip starts on one calendar day and ends on another. Trip timestamps are stored in UTC. Write a PostgreSQL query that calculates driver time for each day from 2024-01-01 through 2024-01-04, inclusive.

Requirements

Split every valid trip at midnight boundaries, so each calendar day receives only the time occurring on that day.
Clip trips to the reporting period and exclude trips with missing timestamps, nonpositive duration, no matching driver, or no overlap with the period.
Return one row per driver and active date, with total time in hours rounded to two decimal places. Sort by driver name and activity date.

Schema

drivers

Column	Type	Description
driver_idPK	INTEGER	Unique Uber driver identifier
driver_name	VARCHAR(100)	Driver display name

trips

Column	Type	Description
trip_idPK	INTEGER	Unique trip identifier
driver_id	INTEGER	Assigned Uber driver identifier
started_at	TIMESTAMP	Trip start timestamp in UTC
ended_at	TIMESTAMP	Trip end timestamp in UTC

Your question is Total Driver Time Per Day. 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