Connecting Itineraries per City Pair

Medium
540.

Connecting Itineraries per City Pair

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

ColumnType
booking_refchar
passenger_namevarchar
routingvarchar
fareint

Primary key: booking_ref.

routing holds two to five three-letter airport codes separated by single hyphens, with no spaces.

Example

The rows must come back in the order shown.

Itinerary

booking_refpassenger_nameroutingfare
AB12CDNehaBLR-HYD-DEL7400
EF34GHRahulBLR-DEL5600
JK56LMTanviBLR-BOM-DEL6900
NP78QRLiamMAA-BLR-BOM-GOI9100
ST90UVZaraCOK-BOM-DEL8800
WX12YZDevMAA-HYD-GOI7200
BC34DFIraCOK-DEL9900

Output

origindestinationconnecting_bookingsmax_stops
BLRDEL21
MAAGOI22
COKDEL11
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
booking_refchar
passenger_namevarchar
routingvarchar
fareint

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_refpassenger_nameroutingfare
AB12CDNehaBLR-HYD-DEL7400
EF34GHRahulBLR-DEL5600
JK56LMTanviBLR-BOM-DEL6900
NP78QRLiamMAA-BLR-BOM-GOI9100
ST90UVZaraCOK-BOM-DEL8800
WX12YZDevMAA-HYD-GOI7200
BC34DFIraCOK-DEL9900

Output

origindestinationconnecting_bookingsmax_stops
BLRDEL21
MAAGOI22
COKDEL11

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, destination

Another 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 →