Connecting Itineraries per City Pair — SQL Strings Problem
An online travel agency stores each itinerary's routing as airport codes joined by hyphens, for example BLR-HYD-DEL (BLR to DEL with one stop in HYD).
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #540
Problem statement
An online travel agency stores each itinerary's routing as airport codes joined by hyphens, for example BLR-HYD-DEL (BLR to DEL with one stop in HYD). The first code is the origin, the last is the destination, and every code in between is a stop.
Considering only itineraries with at least one stop, return for every origin–destination pair: origin, destination, connecting_bookings (how many such itineraries) and max_stops (the most stops any of them makes). Order by connecting_bookings descending, then origin, then destination.
Tables
Table: Itinerary
| Column | Type |
|---|---|
| booking_ref | char |
| passenger_name | varchar |
| routing | varchar |
| fare | int |
Primary key: booking_ref.
routing holds two to five three-letter airport codes separated by single hyphens, with no spaces.
Examples
Example 1
Itinerary
| booking_ref | passenger_name | routing | fare |
|---|---|---|---|
| AB12CD | Neha | BLR-HYD-DEL | 7400 |
| EF34GH | Rahul | BLR-DEL | 5600 |
| JK56LM | Tanvi | BLR-BOM-DEL | 6900 |
| NP78QR | Liam | MAA-BLR-BOM-GOI | 9100 |
| ST90UV | Zara | COK-BOM-DEL | 8800 |
| WX12YZ | Dev | MAA-HYD-GOI | 7200 |
| BC34DF | Ira | COK-DEL | 9900 |
Output
| origin | destination | connecting_bookings | max_stops |
|---|---|---|---|
| BLR | DEL | 2 | 1 |
| MAA | GOI | 2 | 2 |
| COK | DEL | 1 | 1 |
How to solve Connecting Itineraries per City Pair
Everything the question needs is inside the routing string. The origin is the part before the first hyphen, SUBSTRING_INDEX(routing, '-', 1), and the destination the part after the last, SUBSTRING_INDEX(routing, '-', -1). The number of stops is the number of codes minus two, which is the number of hyphens minus one; counting a character is the standard trick LENGTH(s) - LENGTH(REPLACE(s, '-', '')) — remove every hyphen and see how much shorter the string gets.
A direct flight has one hyphen, so hyphens >= 2 keeps exactly the connecting itineraries. Group what is left by the (origin, destination) expressions and take COUNT(*) and the MAX of the stops. Direction matters — BLR→DEL and DEL→BLR are different pairs — which the grouping respects.
Because every code has exactly three letters, the alternative can use positions instead: LEFT(routing, 3), RIGHT(routing, 3), and stops = (length − 7) DIV 4. That only works on fixed-width data, which is why the hyphen-based version is the safer habit. The order is fixed by the statement, with origin and destination breaking ties.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(routing, '-', 1) AS origin,
SUBSTRING_INDEX(routing, '-', -1) AS destination,
COUNT(*) AS connecting_bookings,
MAX(LENGTH(routing) - LENGTH(REPLACE(routing, '-', '')) - 1) AS max_stops
FROM Itinerary
WHERE LENGTH(routing) - LENGTH(REPLACE(routing, '-', '')) >= 2
GROUP BY SUBSTRING_INDEX(routing, '-', 1), SUBSTRING_INDEX(routing, '-', -1)
ORDER BY connecting_bookings DESC, origin, destinationAnother way
SELECT origin, destination, COUNT(*) AS connecting_bookings, MAX(stops) AS max_stops
FROM (SELECT LEFT(routing, 3) AS origin, RIGHT(routing, 3) AS destination, (CHAR_LENGTH(routing) - 7) DIV 4 AS stops FROM Itinerary) t
WHERE stops >= 1
GROUP BY origin, destination
ORDER BY COUNT(*) DESC, origin ASC, destination ASC← Loyalty Points Expiring for Inactive Members · Flights Delayed Three or More Days in a Row →