Get startedGet started for free

Calculate arrival time in minutes

As a data driven developer you may need to make information more accessible to users.

The table train_schedule provides arrival times and station names for two train lines. This is based on data obtained for a commuter rail line that travels between San Francisco and cities to its south.

You are part of a team developing an app to make the train schedule information more useful to commuters. One feature they have requested for the app is an option to see at a glance how long it takes to travel from the first stop on the line to subsequent stops.

This exercise is part of the course

Time Series Analysis in PostgreSQL

View Course

Exercise instructions

  • In the common table expression, get the first arrival_time for each train line, corresponding to the arrival time at the first station on the train line.
  • Using EXTRACT(), calculate the relative difference of each station's arrival compared to the first station on the line, measured in integer minutes.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Get first arrival time for each line
WITH start_times AS
(
	SELECT train_id, ___ AS start_time
	FROM train_schedule
	___
)
SELECT
	train_id,
	station,
	arrival_time,
	-- Relative time difference from first station in minutes
	(___(___ (___))) 
    	___ AS total_minutes
FROM train_schedule JOIN start_times USING(train_id)
ORDER BY train_id, arrival_time;
Edit and Run Code