Drill 2 of 3 in Travel questions
0

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…