DB Exercises
0
← Exercises/
Window Functions•14 of 14
PrevNext
Window FunctionsAdvancedPRO

Driver Idle Turnaround Time Between Completed Trips

Scenario

Fleet dispatch algorithms monitor driver idle time between trips. A long gap between the previous dropoff and the next pickup indicates driver deadheading or localized supply shortages.

Task
For each trip in `driver_trip_logs`, look up the driver's previous trip dropoff time using `LAG()`. Return: 1. `trip_id` 2. `driver_id` 3. `pickup_time` 4. `dropoff_time` 5. `prev_dropoff_time`: `LAG(dropoff_time) OVER (PARTITION BY driver_id ORDER BY pickup_time ASC)` Order results by `driver_id` ascending, then by `pickup_time` ascending.
Expected Output Format

Trip log showing preceding dropoff timestamp per driver.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Driver Idle Turnaround Time Between Completed Trips” SQL Problem

Business Scenario

Fleet dispatch algorithms monitor driver idle time between trips. A long gap between the previous dropoff and the next pickup indicates driver deadheading or localized supply shortages.

Expected Output & Criteria

Trip log showing preceding dropoff timestamp per driver.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE driver_trip_logs (
        trip_id INT PRIMARY KEY,
        driver_id INT NOT NULL,
        pickup_time TIMESTAMP NOT NULL,
        dropoff_time TIMESTAMP NOT NULL
      );

Target SQL Clauses & Operators

LAGOVERPARTITION BY

Want more Window Functions practice?

Explore the full syllabus of 14 exercises, syntax guides, and interview pitfalls.

View Window Functions Track→