Interview Question Bank · AVG per group · RANK() OVER (PARTITION BY)
2. Most delayed airport each day, ties included
MediumTravelQuestion #210
Concept: AVG per group · RANK() OVER (PARTITION BY)show
"The top item per group" is two steps: aggregate to one row per item and group, then rank within each group with RANK() OVER (PARTITION BY group ORDER BY value DESC) and keep rank 1 in an outer query. RANK gives tied rows the same rank, so ties survive; ROW_NUMBER silently picks one. AVG skips NULLs on its own, so don't turn missing values into zeros unless they really are zeros.
Example (different data):
WITH waits AS (
SELECT sale_date, store, AVG(wait_min) AS avg_wait
FROM checkouts
GROUP BY sale_date, store
)
SELECT sale_date, store, avg_wait
FROM (SELECT *, RANK() OVER (PARTITION BY sale_date ORDER BY avg_wait DESC) AS rk
FROM waits) r
WHERE rk = 1;Your task
Ottergate Air's operations team reviews each day's worst-performing departure airport in its morning meeting, and wants every airport that shares the top spot.
For each flight_date in tr_flights, find the origin airport with the highest average dep_delay_min across its flights that operated (status 'landed'). If airports tie, return all of them. Return flight_date, airport, flights (landed departures from that airport that day) and avg_dep_delay_min (rounded to 1 decimal). Sort by flight_date, then airport.
Starting Postgres in your browser…
Checking your access…