DB Exercises
0
← Exercises/
Joins•12 of 12
PrevNext
JoinsAdvancedPRO

Multi-Leg Freight Hub Transit Connections

Scenario

Freight dispatchers need to route cargo shipments between cities that don't have direct connections. They self-join flight route segments to find viable two-leg routes with a transfer hub.

Task
Self-join `flight_routes` (`leg1` and `leg2`) where `leg1.dest_hub = leg2.origin_hub` and `leg1.origin_hub != leg2.dest_hub`. Return: 1. `leg1.origin_hub` AS `start_city` 2. `leg1.dest_hub` AS `layover_hub` 3. `leg2.dest_hub` AS `destination_city` 4. `(leg1.flight_hours + leg2.flight_hours)` AS `total_flight_hours` Filter for routes starting in `'Chicago'`. Order results by `total_flight_hours` ascending, then by `destination_city` ascending.
Expected Output Format

Two-leg transit routes from Chicago with layover hub and total flight hours.

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 “Multi-Leg Freight Hub Transit Connections” SQL Problem

Business Scenario

Freight dispatchers need to route cargo shipments between cities that don't have direct connections. They self-join flight route segments to find viable two-leg routes with a transfer hub.

Expected Output & Criteria

Two-leg transit routes from Chicago with layover hub and total flight hours.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE flight_routes (
        route_id INT PRIMARY KEY,
        origin_hub VARCHAR(30) NOT NULL,
        dest_hub VARCHAR(30) NOT NULL,
        flight_hours NUMERIC(4, 1) NOT NULL
      );

Target SQL Clauses & Operators

JOINONORDER BY

Want more Joins practice?

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

View Joins Track→