Challenge: Total Ride Time for Each Subway Line

The construction company plans to add more stations to the Yellow metro line.

Our task is to determine the total travel time for each metro line. This information is crucial for the company to plan maintenance and expansion of the Yellow line without causing too much inconvenience to passengers.

To find the total travel time, we need to calculate the sum of travel times to each station using the SUM() function.

Note

Calculating the sum of travel times to each station gives us the time it takes for a train to travel from one end of the line to the other. However, to find the total turnaround time (the time for a round trip), we need to multiply this sum by 2.

Let's look at an example using a metro_travel_time table.

Suppose we want to find the line with the most stations.


              1234
            
SELECT line_name, COUNT(station_name) AS station_count
FROM metro_travel_time
GROUP BY line_name
ORDER BY station_count DESC

Let's return to the task because the workers await our verdict on the expansion of the metro station.

Task

Swipe to begin your solution

Calculate the turnaround time for each of the metro lines. To do this, use SUM(), group the data, and order in ascending order.

Note

Name the new column turnaround_time so that your solution can be successfully tested. Also, don't forget to multiply the sum of time by 2.

Brief Instructions

Retrieve the line_name column and the sum of the time_to_next_station column.
For the second column, multiply it by 2 using the formula SUM(time_to_next_station) * 2.
Add the alias turnaround_time to the second column.
Group the data by line_name.
Sort the results by turnaround_time.

Solution

Switch to desktop for real-world practiceContinue from where you are using one of the options below

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 3

Challenge: Total Ride Time for Each Subway Line

The construction company plans to add more stations to the Yellow metro line.

To find the total travel time, we need to calculate the sum of travel times to each station using the SUM() function.

Note

Calculating the sum of travel times to each station gives us the time it takes for a train to travel from one end of the line to the other. However, to find the total turnaround time (the time for a round trip), we need to multiply this sum by 2.

Let's look at an example using a metro_travel_time table.

Suppose we want to find the line with the most stations.


              1234
            
SELECT line_name, COUNT(station_name) AS station_count
FROM metro_travel_time
GROUP BY line_name
ORDER BY station_count DESC

Let's return to the task because the workers await our verdict on the expansion of the metro station.

Task

Swipe to begin your solution

Calculate the turnaround time for each of the metro lines. To do this, use SUM(), group the data, and order in ascending order.

Note

Name the new column turnaround_time so that your solution can be successfully tested. Also, don't forget to multiply the sum of time by 2.

Brief Instructions

Retrieve the line_name column and the sum of the time_to_next_station column.
For the second column, multiply it by 2 using the formula SUM(time_to_next_station) * 2.
Add the alias turnaround_time to the second column.
Group the data by line_name.
Sort the results by turnaround_time.

Solution

Switch to desktop for real-world practiceContinue from where you are using one of the options below

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 3

Switch to desktop for real-world practiceContinue from where you are using one of the options below