Passengers Who Flew a Route in Both Directions

Medium
537.

Passengers Who Flew a Route in Both Directions

MediumJoinsStrings

MySQL

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

ColumnType
passenger_idint
passenger_namevarchar

Primary key: passenger_id.

One row per traveller in the frequent-flyer database.

Table: FlightSegment

ColumnType
segment_idint
passenger_idint
originchar
destinationchar
travel_datedate

Primary key: segment_id.

One row per flown segment; airport codes are three upper-case letters and origin never equals destination.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Passenger

passenger_idpassenger_name
1Anjali
2Karan
3Emma

FlightSegment

segment_idpassenger_idorigindestinationtravel_date
11DELBLR2024-02-02
21BLRDEL2024-02-05
31DELBLR2024-03-11
42BOMGOI2024-02-10
53BOMGOI2024-02-14
62BOMBLR2024-02-20
73GOIBOM2024-02-18
81BOMMAA2024-04-01

Output

passenger_idpassenger_namecity_pair
1AnjaliBLR-DEL
3EmmaBOM-GOI
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.

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

ColumnType
passenger_idint
passenger_namevarchar

Primary key: passenger_id.

One row per traveller in the frequent-flyer database.

Table: FlightSegment

ColumnType
segment_idint
passenger_idint
originchar
destinationchar
travel_datedate

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_idpassenger_name
1Anjali
2Karan
3Emma

FlightSegment

segment_idpassenger_idorigindestinationtravel_date
11DELBLR2024-02-02
21BLRDEL2024-02-05
31DELBLR2024-03-11
42BOMGOI2024-02-10
53BOMGOI2024-02-14
62BOMBLR2024-02-20
73GOIBOM2024-02-18
81BOMMAA2024-04-01

Output

passenger_idpassenger_namecity_pair
1AnjaliBLR-DEL
3EmmaBOM-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.destination

Another 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) = 2

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