lesson 35 Window functions · advanced

Window functions: ROW_NUMBER

Assign a sequence number to rows within a group.

the idea

A window function computes a value for each row from a set of related rows, without collapsing them. ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) numbers rows 1, 2, 3 … within each partition in the given order. Add a tie-breaker so the numbering is deterministic.

Dataset: U.S. Flights

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Follow one plane through the day. Run this. You see every leg for one busy aircraft on 2025-01-15, sorted by when it was scheduled to leave—same plane, many flights in order.

   step-1.sql Run

   sql›

   SELECT flight_id, tail_number, origin, destination, scheduled_departure
   FROM flights
   WHERE flight_date = '2025-01-15' AND tail_number = (
     SELECT tail_number
     FROM flights
     WHERE flight_date = '2025-01-15' AND tail_number IS NOT NULL
     GROUP BY tail_number
     ORDER BY COUNT(*) DESC, tail_number
     LIMIT 1
   )
   ORDER BY scheduled_departure;

2. 2

   Number departures at each airport. Run this. ROW_NUMBER() assigns 1, 2, 3… within each origin airport, ordered by departure time. That is how you label “first DEN departure,” “second DEN departure,” and so on.the pattern

   example.sql Run

   sql›

   SELECT
     flight_id,
     origin,
     scheduled_departure,
     ROW_NUMBER() OVER (PARTITION BY origin ORDER BY scheduled_departure, flight_id) AS departure_number
   FROM flights
   WHERE flight_date = '2025-01-15' AND origin = 'DEN';

common mistake

Without ORDER BY inside OVER, the numbering order is undefined; a different plan can number the same rows differently.