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…