ExpertCTEs & Recursion50 points

Lahore to New York with at most two stops

A customer in Lahore (LHE) wants to reach New York (JFK) and no direct route exists. The sales team needs every possible itinerary built from the routes table with at most 2 stops (at most 3 legs), never visiting the same airport twice, and never flying on after landing at JFK. Each leg uses one directed row of routes.

Task: use a recursive CTE to build the itineraries and return itinerary (airport codes joined by >, for example LHE>DXB>JFK), stops (number of intermediate airports) and total_km (sum of the leg distances). Only itineraries that end at JFK. Sort by total_km ascending, then itinerary 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.

aircraft14 rows

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.

airports12 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.

bookings557 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.

flights260 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.

passengers45 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.

routes40 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?

Carry the path so far as text, and stop recursing when the leg count reaches the limit or you arrive at JFK.

SQL · SQLite

50 pts

Loading editor...

Run your code to see the output here. Ctrl + Enter also runs it.