London, or as the Romans called it "Londonium"! As of 2021, Greater London is 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 have 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 makes 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 Databricks database containing a schema called tfl with a single table called journeys. The table, which you will use for the project, includes the following data:
tfl.journeys
tfl.journeys| Column | Definition | Data type |
|---|---|---|
month | Month in number format, e.g., 1 equals January | integer |
year | Year | integer |
days | Number of days in the given month | integer |
report_date | Date that the data was reported | date |
journey_type | Method of transport used | varchar |
journeys_millions | Millions of journeys, measured in decimals | float |
You will execute SQL queries to answer three questions, as listed in the instructions.
-- most_popular_transport_types
-- modify the query below as required
SELECT *
FROM tfl.journeys
LIMIT 5;SELECT
journey_type,
SUM(journeys_millions) as total_journeys_millions
FROM tfl.journeys
-- Group by type of journey and order the results
GROUP BY journey_type
ORDER BY total_journeys_millions DESC;
-- most_popular_transport_types
-- Copy code into first SQL cell
-- Get the journey type and total number of journeys per type
SELECT month,
year,
-- Round journeys_millions to two decimal places and alias the column name
ROUND(journeys_millions,2) AS rounded_journeys_millions
FROM tfl.journeys
-- Filter for cable car journeys and remove dates with zero journeys
WHERE journey_type = 'Emirates Airline' AND journeys_millions IS NOT NULL
ORDER BY rounded_journeys_millions DESC
LIMIT 5;
-- least_popular_years_tube
SELECT year,
journey_type,
SUM(journeys_millions) as total_journeys_millions
FROM tfl.journeys
-- Filter for Underground & DLR journeys
WHERE journey_type LIKE '%Underground%'
GROUP BY year, journey_type
ORDER BY total_journeys_millions
LIMIT 5;