ExpertJoins50 points

Hub detection with a self join

Each row of routes is one direction: LHR to JFK and JFK to LHR are two rows, and a few routes exist in one direction only. Network analysts call an airport a hub when it has at least 4 round-trip partners: other airports X where the airline flies both this airport to X and X back to this airport.

Task: join routes to itself (a LEFT JOIN on the reverse direction) and, per origin airport, count outbound_routes (routes leaving it), round_trip_partners (outbound routes that also have a return route) and one_way_outbound (outbound routes with no return route). Keep airports with round_trip_partners of 4 or more and return code, city (from airports), outbound_routes, round_trip_partners and one_way_outbound. Sort by round_trip_partners from high to low, then code 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.

SQL · SQLite

50 pts

Loading editor...

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