-- Load data from a UK-wide GTFS into postgres using https://github.com/fitnr/gtfs-sql-importer
-- Used this GTFS file:
-- https://transitfeeds.com/p/association-of-train-operating-companies/284

with terminals as ( -- ID codes for London terminal stations
   values ('BFR'), ('CST'), ('CHX'), ('CTK'), ('EUS'), ('FST'), ('KGX'), ('LST'), ('LBG'), ('MYB'), ('MOG'), ('OLD'), ('PAD'), ('STP'), ('SPX'), ('VXH'), ('VIC'), ('WAT'), ('WAE')
)
, london_trips as ( -- select trips that include the above codes
	select distinct feed_index, route_id, trip_id
	from gtfs.stops
		left join gtfs.stop_times using (feed_index, stop_id)
		left join gtfs.trips using (feed_index, trip_id)
	where feed_index = 1
		and stop_id IN (select * from terminals)
) -- select stops that fall on the above trips, summarize at the route level
select stop_id, stop_name, count(distinct route_id)
from london_trips
	left join gtfs.stop_times using (feed_index, trip_id)
	left join gtfs.stops using (feed_index, stop_id)
where stop_id not in (select * from terminals)
group by feed_index, stop_id
having count(distinct route_id) > 0;

-- returns 1179 rows, out of 2966 non-terminal stations

/*
stations served by the most routes that travel to a terminal station:
| CLJ  | Clapham Junction | 181  |
| ECR  | East Croydon     | 129  |
| FPK  | Finsbury Park    | 123  |
| AAP  | Alexandra Palace | 120  |
| NWX  | New Cross        | 107  |
| PUR  | Purley           | 106  |
*/