Passengers Who Flew a Route in Both Directions — SQL Problem
The network-planning team wants to know which travellers fly a city pair both ways — for example BLR→DEL on one ticket and DEL→BLR on another, on any dates.
- Difficulty: Medium
- Topics: Joins, Strings
- Dialect: MySQL
- Problem: #537
Problem statement
The network-planning team wants to know which travellers fly a city pair both ways — for example BLR→DEL on one ticket and DEL→BLR on another, on any dates.
Return passenger_id, passenger_name and city_pair for every passenger and every pair of airports they flew in both directions. Write city_pair as the two airport codes in alphabetical order joined by a hyphen (BLR-DEL, never DEL-BLR), and list each passenger and pair once, however many times it was flown. Return the rows in any order.
Tables
Table: Passenger
| Column | Type |
|---|---|
| passenger_id | int |
| passenger_name | varchar |
Primary key: passenger_id.
One row per traveller in the frequent-flyer database.
Table: FlightSegment
| Column | Type |
|---|---|
| segment_id | int |
| passenger_id | int |
| origin | char |
| destination | char |
| travel_date | date |
Primary key: segment_id.
One row per flown segment; airport codes are three upper-case letters and origin never equals destination.
Examples
Example 1
Passenger
| passenger_id | passenger_name |
|---|---|
| 1 | Anjali |
| 2 | Karan |
| 3 | Emma |
FlightSegment
| segment_id | passenger_id | origin | destination | travel_date |
|---|---|---|---|---|
| 1 | 1 | DEL | BLR | 2024-02-02 |
| 2 | 1 | BLR | DEL | 2024-02-05 |
| 3 | 1 | DEL | BLR | 2024-03-11 |
| 4 | 2 | BOM | GOI | 2024-02-10 |
| 5 | 3 | BOM | GOI | 2024-02-14 |
| 6 | 2 | BOM | BLR | 2024-02-20 |
| 7 | 3 | GOI | BOM | 2024-02-18 |
| 8 | 1 | BOM | MAA | 2024-04-01 |
Output
| passenger_id | passenger_name | city_pair |
|---|---|---|
| 1 | Anjali | BLR-DEL |
| 3 | Emma | BOM-GOI |
How to solve Passengers Who Flew a Route in Both Directions
A round trip shows up as two rows of FlightSegment for one passenger with origin and destination swapped, so a self join on b.passenger_id = a.passenger_id AND b.origin = a.destination AND b.destination = a.origin finds every such match.
Each match is found twice — the outbound row pairs with the return, and the return pairs with the outbound. Keeping only the side where a.origin < a.destination removes the mirror image and, at the same time, makes CONCAT(a.origin, '-', a.destination) the alphabetical spelling the statement wants. A passenger who flew BLR→DEL twice and DEL→BLR once still produces several joined rows, so DISTINCT collapses them to one per passenger and pair.
The first alternative avoids the join: group a passenger's segments by the unordered pair (LEAST / GREATEST of the two codes) and keep the groups with two distinct origins — that can only happen if both directions were flown. The second uses EXISTS for the return leg. The self join is quadratic per passenger in the worst case; the grouping is a single sort.
Reference solution (MySQL)
SELECT DISTINCT p.passenger_id, p.passenger_name, CONCAT(a.origin, '-', a.destination) AS city_pair
FROM FlightSegment a
JOIN FlightSegment b
ON b.passenger_id = a.passenger_id AND b.origin = a.destination AND b.destination = a.origin
JOIN Passenger p ON p.passenger_id = a.passenger_id
WHERE a.origin < a.destinationAnother way
SELECT p.passenger_id, p.passenger_name, t.city_pair
FROM (SELECT passenger_id, origin, CONCAT(LEAST(origin, destination), '-', GREATEST(origin, destination)) AS city_pair FROM FlightSegment) t
JOIN Passenger p ON p.passenger_id = t.passenger_id
GROUP BY p.passenger_id, p.passenger_name, t.city_pair
HAVING COUNT(DISTINCT t.origin) = 2Another way
SELECT p.passenger_id, p.passenger_name, x.city_pair
FROM Passenger p
JOIN (SELECT DISTINCT passenger_id, CONCAT(origin, '-', destination) AS city_pair FROM FlightSegment s
WHERE origin < destination AND EXISTS (SELECT 1 FROM FlightSegment r
WHERE r.passenger_id = s.passenger_id AND r.origin = s.destination AND r.destination = s.origin)) x
ON x.passenger_id = p.passenger_id← Hotel Cancellation Refunds Under a Tiered Policy · Double-Booked Hotel Rooms →