Skip to content
Project: Exploring London's Travel Network
  • AI Chat
  • Code
  • Report
  • London, or as the Romans called it "Londonium"! Home to over 8.5 million residents who speak over 300 languages. While the City of London is a little over one square mile (hence its nickname "The Square Mile"), Greater London has grown to encompass 32 boroughs spanning a total area of 606 square miles!

    Given the city's roads were originally designed for horse and cart, this area and population growth has required the development of an efficient public transport system! Since the year 2000, this has been through the local government body called Transport for London, or TfL, which is managed by the London Mayor's office. Their remit covers the London Underground, Overground, Docklands Light Railway (DLR), buses, trams, river services (clipper and Emirates Airline cable car), roads, and even taxis.

    The Mayor of London's office make their data available to the public here. In this project, you will work with a slightly modified version of a dataset containing information about public transport journey volume by transport type.

    The data has been loaded into a Google BigQuery database called TFL with a single table called JOURNEYS, including the following data:

    TFL.JOURNEYS

    ColumnDefinitionData type
    MONTHMonth in number format, e.g., 1 equals JanuaryINTEGER
    YEARYearINTEGER
    DAYSNumber of days in the given monthINTEGER
    REPORT_DATEDate that the data was reportedDATE
    JOURNEY_TYPEMethod of transport usedVARCHAR
    JOURNEYS_MILLIONSMillions of journeys, measured in decimalsFLOAT

    Note that the table name is upper case* by default.

    You will execute SQL queries to answer three questions, as listed in the instructions.

    Unknown integration
    DataFrameavailable as
    most_popular_transport_types
    variable
    -- most_popular_transport_types
    SELECT JOURNEYS.journey_type, SUM(JOURNEYS.journeys_millions) AS total_journeys_millions
    FROM TFL.JOURNEYS
    GROUP BY JOURNEYS.journey_type
    ORDER BY total_journeys_millions DESC;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    emirates_airline_popularity
    variable
    -- emirates_airline_popularity
    SELECT  
    	JOURNEYS.month,
    	JOURNEYS.year, 
    	ROUND(JOURNEYS.journeys_millions, 2) AS rounded_journeys_millions
    	
    FROM TFL.JOURNEYS
    WHERE 
    	JOURNEYS.journeys_millions IS NOT NULL AND JOURNEYS.journey_type = 'Emirates Airline'
    ORDER BY 
    	rounded_journeys_millions DESC, 
    	JOURNEYS.year ASC
    LIMIT 5;
    
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
    Unknown integration
    DataFrameavailable as
    least_popular_years_tube
    variable
    -- least_popular_years_tube
    SELECT 
    	JOURNEYS.year,
    	JOURNEYS.journey_type, 
    	SUM(JOURNEYS.journeys_millions) AS total_journeys_millions
    FROM TFL.JOURNEYS
    WHERE JOURNEYS.journey_type = 'Underground & DLR'
    GROUP BY 
    	JOURNEYS.year,
    	JOURNEYS.journey_type 
    ORDER BY total_journeys_millions ASC
    LIMIT 5;
    This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.