Window Function Sensor Max | Dataford Interview Questions

A weather-monitoring platform needs a report of each sensor station's highest daily temperature during the previous calendar month. Write a SQL query to identify every station-day whose daily maximum temperature is the station's highest in that reporting period.

Output

One row per station and peak-temperature date, with columns station_id, station_name, reading_date, and highest_daily_temperature.
Include only readings from the previous calendar month relative to the fixed reporting date of September 15, 2026. Rows with a NULL temperature are ignored when calculating daily maximums.
Include every date tied for a station's highest daily temperature. Return no row for stations without a non-NULL reading in the period.
Order by station_id, then reading_date ascending.

Schema

sensor_stations

Column	Type	Description
station_idPK	INT	Unique sensor station identifier
station_name	VARCHAR(100)	Sensor station name
region	VARCHAR(80)	Geographic region containing the station

temperature_readings

Column	Type	Description
reading_idPK	INT	Unique temperature reading identifier
station_id	INT	Sensor station that produced the reading
recorded_at	TIMESTAMP	Timestamp when the temperature was recorded
temperature_celsius	NUMERIC(5,2)	Temperature measurement in degrees Celsius

Your question is Window Function Sensor Max. 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.

Problem