DB Exercises
0
← Exercises/
CTEs•7 of 12
PrevNext
CTEsAdvancedPRO

Cumulative Route Leg Transit & Total Duration

Scenario

Regional freight dispatchers monitor multi-stop delivery routes to ensure drivers do not exceed mandatory regulatory driving shift limits of 480 minutes (8 hours).

Task
Write a query using a CTE to aggregate total transit time per delivery route in `route_stops`. Calculate: 1. `route_id` 2. `stop_count`: Total number of stops on the route 3. `total_driving_minutes`: Sum of `leg_minutes` 4. `exceeds_shift_limit`: Boolean flag `CASE WHEN SUM(leg_minutes) > 480 THEN true ELSE false END` Order results by `total_driving_minutes` descending.
Expected Output Format

Summary of stops, total driving time, and shift violation status per route.

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 “Cumulative Route Leg Transit & Total Duration” SQL Problem

Business Scenario

Regional freight dispatchers monitor multi-stop delivery routes to ensure drivers do not exceed mandatory regulatory driving shift limits of 480 minutes (8 hours).

Expected Output & Criteria

Summary of stops, total driving time, and shift violation status per route.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE route_stops (
        stop_id INT PRIMARY KEY,
        route_id INT NOT NULL,
        stop_sequence INT NOT NULL,
        leg_minutes INT NOT NULL
      );

Target SQL Clauses & Operators

WITHGROUP BYSUM

Want more CTEs practice?

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

View CTEs Track→