Out and back within a week: round-trip travellers
The ancillary sales team wants to target business travellers who fly out and come back within days. A round trip is a pair of completed flights taken by the same passenger where the second flight flies the exact reverse route of the first (for example LHR to JFK then JFK to LHR) and departs between 1 and 7 calendar days after the first one (compare the date parts of sched_dep, same-day returns do not count). Each different pair of bookings counts as one round trip.
Task: self-join bookings, flights and routes to find those pairs and return passenger (name), round_trips (number of pairs) and total_fare_usd (the sum of fare_usd of both bookings in every pair, a pair's two fares added together). Sort by round_trips from high to low, then total_fare_usd from high to low, then passenger A to Z.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| id INTEGER | airline TEXT | model TEXT | seats INTEGER | delivered_year INTEGER |
|---|---|---|---|---|
| 1 | SkyMeridian | A320 | 180 | 2017 |
| 2 | SkyMeridian | A320 | 180 | 2019 |
| 3 | SkyMeridian | A321 | 200 | 2021 |
| 4 | SkyMeridian | B787-9 | 290 | 2018 |
| 5 | SkyMeridian | A350-900 | 320 | 2022 |
| 6 | SkyMeridian | B787-9 | 290 | 2020 |
Showing the first 6 of 14 rows.
| code TEXT | name TEXT | city TEXT | country TEXT |
|---|---|---|---|
| JFK | John F. Kennedy International | New York | USA |
| LHR | Heathrow | London | United Kingdom |
| DXB | Dubai International | Dubai | UAE |
| SIN | Changi | Singapore | Singapore |
| LHE | Allama Iqbal International | Lahore | Pakistan |
| ISB | Islamabad International | Islamabad | Pakistan |
Showing the first 6 of 12 rows.
| id INTEGER | flight_id INTEGER | passenger_id INTEGER | cabin TEXT | fare_usd INTEGER |
|---|---|---|---|---|
| 1 | 1 | 14 | Economy | 230 |
| 2 | 1 | 38 | Economy | 180 |
| 3 | 2 | 1 | Economy | 380 |
| 4 | 2 | 22 | Economy | 400 |
| 5 | 2 | 45 | Economy | 445 |
| 6 | 3 | 1 | Economy | 450 |
Showing the first 6 of 557 rows.
| id INTEGER | flight_no TEXT | route_id INTEGER | aircraft_id INTEGER | sched_dep TEXT | dep_delay_min INTEGER | status TEXT |
|---|---|---|---|---|---|---|
| 1 | FC185 | 3 | 12 | 2025-01-02 07:15:00 | 0 | Completed |
| 2 | OW370 | 8 | 10 | 2025-01-03 05:30:00 | 40 | Completed |
| 3 | SM489 | 35 | 5 | 2025-01-03 08:00:00 | 25 | Completed |
| 4 | SM415 | 33 | 6 | 2025-01-03 14:45:00 | 10 | Completed |
| 5 | OW592 | 14 | 9 | 2025-01-08 16:00:00 | 50 | Completed |
| 6 | SM222 | 4 | 2 | 2025-01-09 08:45:00 | 35 | Completed |
Showing the first 6 of 260 rows.
| id INTEGER | name TEXT | home_country TEXT | loyalty_tier TEXT |
|---|---|---|---|
| 1 | Ayesha Khan | Pakistan | Silver |
| 2 | Bilal Okafor | Australia | Silver |
| 3 | Chen Silva | Germany | NULL |
| 4 | Diego Tanaka | Australia | Silver |
| 5 | Elena Weber | Germany | NULL |
| 6 | Farid Nguyen | Australia | Gold |
Showing the first 6 of 45 rows.
| id INTEGER | origin TEXT | dest TEXT | distance_km INTEGER |
|---|---|---|---|
| 1 | LHE | DXB | 2100 |
| 2 | DXB | LHE | 2100 |
| 3 | ISB | DXB | 2300 |
| 4 | DXB | ISB | 2300 |
| 5 | DXB | LHR | 5500 |
| 6 | LHR | DXB | 5500 |
Showing the first 6 of 40 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
Join the same table twice with different aliases and let the date difference decide which side is the outbound flight.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.