Bus routes 🚌

Scenario

A transport company records their bus routes in a table where each row represents a leg of a journey, but they want to know the full route for each bus.

Question

Compute the full route for each bus.

Use the following stops as the first stop for each bus:

• Old Street for bus_id = 1
• Hillside for bus_id = 2
• Birch Park for bus_id = 3

The output should have a single row per bus with the columns:

• bus_id
• route which is each bus stop in the route separated by a hyphen, e.g. First Stop - Middle Stop - Final Stop

Order the output by bus_id.

Expand for the DDL

create table bus_stops (
    bus_id    int,
    from_stop varchar,
    to_stop   varchar,
    primary key (bus_id, from_stop)
);
insert into bus_stops
values
    (1, 'Bakers March',   'West Quay Stop'),
    (3, 'Birch Park',     'Farfair'),
    (1, 'Cavendish Road', 'Bakers March'),
    (3, 'Cavendish Road', 'Birch Park'),
    (1, 'Crown Street',   'Leather Lane'),
    (3, 'Farfair',        'Golden Lane'),
    (2, 'Fellows Road',   'Riverside'),
    (2, 'Furlong Reach',  'Hillside'),
    (3, 'Golden Lane',    'Goose Green'),
    (1, 'Goose Green',    'Crown Street'),
    (3, 'Goose Green',    'Sailors Rest'),
    (2, 'Hillside',       'Fellows Road'),
    (2, 'Laddersmith',    'Furlong Reach'),
    (1, 'Leather Lane',   'Old Street'),
    (1, 'Old Street',     'Cavendish Road'),
    (2, 'Riverside',      'Laddersmith'),
    (3, 'Sailors Rest',   'Cavendish Road'),
    (1, 'West Quay Stop', 'Goose Green')
;

The solution can be found at:

• bus-routes.md

---

Sample input

Compute the full route for each bus, using the following stops as the first stop for each bus:

• Stop A for bus_id = 1
• First Stop for bus_id = 2

bus_id	from_stop	to_stop
1	Stop A	Stop B
1	Stop B	Stop C
1	Stop C	Stop A
2	First Street	Second Street
2	Second Street	Third Street
2	Third Street	First Street

with bus_stops(bus_id, from_stop, to_stop) as (
    values
        (1, 'Stop A',        'Stop B'),
        (1, 'Stop B',        'Stop C'),
        (1, 'Stop C',        'Stop A'),
        (2, 'First Street',  'Second Street'),
        (2, 'Second Street', 'Third Street'),
        (2, 'Third Street',  'First Street')
)

Sample output

bus_id	route
1	Stop A - Stop B - Stop C
2	First Street - Second Street - Third Street

solution(bus_id, route) as (
    values
        (1, 'Stop A - Stop B - Stop C'),
        (2, 'First Street - Second Street - Third Street');
)

Hint 1

Use a recursive CTE to put the bus stops in order for each bus.

Hint 2

These routes are cyclical, so include a condition to stop the recursion when you reach the first stop for each bus again.