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.
Questo esercizio fa parte del corso
Time Series Analysis in PostgreSQL
Istruzioni dell'esercizio
- 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.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
-- 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;