Drill 3 of 3 in Travel questions
0

Interview Question Bank · Self-join · reversed match · one direction only

3. Round trips: outbound and return legs

MediumTravelQuestion #212

Concept: Self-join · reversed match · one direction onlyshow

Pairing rows of the same table is a self-join: join the table to itself under two aliases and spell out how the second row relates to the first. For a reversed pair, both ends have to swap (b.from = a.to AND b.to = a.from), or one-way connections slip in. Add an ordering condition such as b.date > a.date, or every pair shows up twice, once from each side.

Example (different data):

SELECT a.from_acct, a.to_acct, a.amount, b.amount AS sent_back, b.sent_on - a.sent_on AS days_later
FROM transfers a
JOIN transfers b ON b.from_acct = a.to_acct
  AND b.to_acct = a.from_acct
  AND b.sent_on > a.sent_on;

Your task

Ottergate Air's loyalty team wants a list of round trips flown in early March, to test a return-flight discount.

A round trip is two landed flights (status 'landed') ticketed to the same passenger, where the second flies the reverse route (its origin is the first's destination and its destination is the first's origin) on a later flight_date. Return passenger (name from tr_passengers), outbound_flight and outbound_date (the first flight's flight_no and flight_date), return_flight, return_date and trip_days (return_date - outbound_date). Sort by outbound_date, then passenger.

Starting Postgres in your browser…

Checking your access…