SQL Average Delay by Time | Dataford Interview Questions

Write a SQL query to calculate the average delay time per line, grouped by time of day for Transport for London.

Use the supplied TfL line and service event data. Include only completed events with a recorded delay.

Output

One row per line and time-of-day category, with columns line_name, time_of_day, and average_delay_minutes.
Sort by line name alphabetically, then Morning, Afternoon, Evening, and Night.

Classify hours 5 through 11 as Morning, 12 through 16 as Afternoon, 17 through 20 as Evening, and all other hours as Night.
Round average_delay_minutes to 2 decimal places.
Include zero-valued delays when calculating the average.

tfl_lines

Column	Type	Description
line_idPK	INT	Unique identifier for a TfL line
line_name	VARCHAR(50)	TfL line name

service_events

Column	Type	Description
event_idPK	INT	Unique service event identifier
line_id	INT	TfL line associated with the event
recorded_at	TIMESTAMP	Timestamp when the event was recorded
delay_minutes	NUMERIC(8,2)	Recorded delay in minutes
status	VARCHAR(20)	Service event status

Your question is SQL Average Delay by Time. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.