lesson 39 Window functions · advanced

Compare rows with LAG

Compare each row with an earlier row.

the idea

LAG(column) returns the value from the previous row in the window order; LEAD returns the next. With PARTITION BY tail_number ORDER BY scheduled_departure, the previous row is the same aircraft’s previous flight, so you can compare consecutive legs.

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

   Look at the previous leg. Run this. LAG() pulls the prior row’s destination for the same tail number—handy for spotting whether origin matches where the plane last landed.

   step-1.sql Run

   sql›

   SELECT
     flight_id,
     tail_number,
     origin,
     destination,
     LAG(destination) OVER (PARTITION BY tail_number ORDER BY scheduled_departure, flight_id) AS previous_destination
   FROM flights
   WHERE flight_date = '2025-01-15' AND tail_number IS NOT NULL
   LIMIT 20;

2. 2

   Compare delay to the last arrival. Run this. For each operated flight you see this leg’s departure delay beside the previous leg’s arrival delay on that aircraft. The first leg of the day shows NULL for the lag column.

   step-2.sql Run

   sql›

   SELECT
     flight_id,
     tail_number,
     departure_delay,
     LAG(arrival_delay) OVER (PARTITION BY tail_number ORDER BY scheduled_departure, flight_id) AS previous_arrival_delay
   FROM flights
   WHERE flight_date = '2025-01-15'
     AND cancelled = 0
     AND tail_number IS NOT NULL
   LIMIT 20;

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   SELECT
     flight_id,
     tail_number,
     origin,
     destination,
     LAG(destination) OVER (PARTITION BY tail_number ORDER BY scheduled_departure, flight_id) AS previous_destination
   FROM flights
   WHERE flight_date = '2025-01-15' AND tail_number IS NOT NULL;

common mistake

The first row of each partition has no previous row: LAG returns NULL, not zero. Local times are only comparable at the same airport, which is exactly where an aircraft arrives and departs again.