Using SQL Window Functions | Dataford Interview Questions

Your question is Using SQL Window Functions. Start with the requirements and the two tables on the right.

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

Problem

Transport for London wants to monitor short-term changes in passenger entries across its Underground stations. Write a PostgreSQL query that aggregates entries by station and date, then calculates a three-record rolling average for each station.

Requirements

Join passenger_entries to stations to return the station name and line.
Restrict the analysis to entries from 1 January 2025 through 5 January 2025, aggregate duplicate records for the same station and date, and treat NULL entry counts as zero.
Use a window function partitioned by station and ordered by date to calculate the average for the current and previous two recorded service days.
Return the station name, date, daily entries, and rolling average, ordered by station name and date.

Schema

stations

Column	Type	Description
station_idPK	INTEGER	Unique TfL station identifier
station_name	VARCHAR(100)	Underground station name
line	VARCHAR(50)	Primary Underground line serving the station

passenger_entries

Column	Type	Description
entry_idPK	INTEGER	Unique passenger entry record identifier
station_id	INTEGER	Station associated with the entry record
entry_date	DATE	Date on which entries were recorded
entry_count	INTEGER	Number of passenger entries, which may be NULL