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
Exercise instructions
- In the common table expression, get the first
arrival_timefor 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;