dbdbexercises
Practice Free
← Syllabus|
Hard

16. Multi-Leg Shipment Route Mileage Accumulator

HardCTEsPostgreSQL 16PRO
Table: shipment_legs
+-------------+-------------+
| Column Name | Type        |
+-------------+-------------+
| cargo_id    | varchar(10) |
| leg_number  | int         |
| port_code   | varchar(10) |
| leg_miles   | int         |
+-------------+-------------+

cargo_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Logistics container tracking systems follow shipping cargo transfers across intermediate transshipment ports, summing cumulative nautical distance from origin to destination.

Write a recursive CTE to trace the delivery chain starting from the origin port where `leg_number = 1`. Accumulate `cumulative_miles` by adding each leg's distance. Return: 1. `cargo_id` 2. `leg_number` 3. `port_code` 4. `cumulative_miles` Order results by `cargo_id` ascending, then by `leg_number` ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Recursive routing hops with cumulative transit mileage.

Constraints:
  • Query must utilize clause(s): WITH RECURSIVEUNION ALL
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “Multi-Leg Shipment Route Mileage Accumulator” SQL Problem

Business Scenario

Logistics container tracking systems follow shipping cargo transfers across intermediate transshipment ports, summing cumulative nautical distance from origin to destination.

Expected Output & Criteria

Recursive routing hops with cumulative transit mileage.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE shipment_legs (
        cargo_id VARCHAR(10) NOT NULL,
        leg_number INT NOT NULL,
        port_code VARCHAR(10) NOT NULL,
        leg_miles INT NOT NULL,
        PRIMARY KEY (cargo_id, leg_number)
      );

Target SQL Clauses & Operators

WITH RECURSIVEUNION ALL

Want more CTEs practice?

Explore the full syllabus of 16 exercises and syntax guides.

View CTEs Track