shack attack (Underground)

shack_attack_underground.sql

-- use with miklcct/journey_recorder

select rank() over (order by `first visited`) as sequence, station, `first visited`, `last visited`, `number of visits`
from (
    select station
    , convert_tz(min(`time stamp`), @@session.time_zone, 'Europe/London') as `first visited`
    , convert_tz(max(`time stamp`), @@session.time_zone, 'Europe/London') as `last visited`
    , count(0) as `number of visits`
    from (
        select case
            when `station` in ('Euston (Bank branch)', 'Euston (Charing Cross branch)') then 'Euston'
            when `station` = 'Hammersmith' and route in ('Circle', 'Hammersmith & City') then 'Hammersmith (Hammersmith & City)'
            when `station` = 'Hammersmith' and route in ('District', 'Piccadilly') then 'Hammersmith (District & Piccadilly)'
            when station = 'Paddington (Circle & District)' or `station` = 'Paddington' and route in ('District', 'Bakerloo') then 'Paddington (Bakerloo, Circle & District)'
            when `station` = 'Paddington' and route in ('Hammersmith & City') then 'Paddington (Hammersmith & City)'
            when `station` = 'Edgware Road' and route in ('District', 'Circle', 'Hammersmith & City') then 'Edgware Road (Circle)'
            when `station` = 'Edgware Road' and route in ('Bakerloo') then 'Edgware Road (Bakerloo)'
            else `station`
        end as station
        , `time stamp`
        from (
            select network, route, `boarding place` as station, `boarding time stamp` as `time stamp`
            from journeys
            union select network, route, `alighting place` as station, `alighting time stamp` as `time stamp`
            from journeys
        ) as `usage`
        where network = 'London Underground'
    ) as usage_with_disambiguation 
    group by station
) as source
order by `first visited`;

According to https://diamondgeezer.blogspot.com/2017/12/how-many-tube-stations-are-there.html , there are two Hammersmith, two Paddington, while Bank & Monument are still officially 2 stations.