1. Learn
    2. /
  2. Courses
    3. /
  3. Time Series Analysis in PostgreSQL

Exercise

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.

Instructions

100 XP
  • 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.