runtime
77 / 139

task LAG / LEAD · medium

Predict: the previous leg of the same aircraft

Predict previous_destination for each sample flight.

what to return

Columns
flight_idtail_numberprevious_destination in this order
Rows
in the order asked
Watch for
Use only the sample rows shown. NULL is not zero or empty text. Keep duplicate rows.
Skill
Reason about the exact result of a fixed SQL query. Refresher: Compare rows with LAG →

data you can query

U.S. Flights dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.

rotation_sample5 columns · 6 rows
flight_idtail_numberorigindestinationscheduled_departuredate & time
19237N125HQINDPHL2025-01-15 16:01
19449N125HQPHLLGA2025-01-15 18:58
19566N125HQLGAORF2025-01-15 21:00
18437N127DUIDASLC2025-01-15 06:00
18618N127DUSLCSNA2025-01-15 08:05
18786N127DUSNASLC2025-01-15 10:08
flight_id
19237
tail_number
N125HQ
origin
IND
destination
PHL
scheduled_departuredate & time
2025-01-15 16:01
flight_id
19449
tail_number
N125HQ
origin
PHL
destination
LGA
scheduled_departuredate & time
2025-01-15 18:58
flight_id
19566
tail_number
N125HQ
origin
LGA
destination
ORF
scheduled_departuredate & time
2025-01-15 21:00
flight_id
18437
tail_number
N127DU
origin
IDA
destination
SLC
scheduled_departuredate & time
2025-01-15 06:00
flight_id
18618
tail_number
N127DU
origin
SLC
destination
SNA
scheduled_departuredate & time
2025-01-15 08:05
flight_id
18786
tail_number
N127DU
origin
SNA
destination
SLC
scheduled_departuredate & time
2025-01-15 10:08

all 6 rows

predict.sql
SELECT
  flight_id,
  tail_number,
  LAG(destination) OVER (PARTITION BY tail_number ORDER BY scheduled_departure, flight_id) AS previous_destination
FROM rotation_sample
ORDER BY tail_number, scheduled_departure, flight_id
Read the query · predict its result
SELECT flight_id, tail_number, LAG(destination) OVER (PARTITION BY tail_number ORDER BY scheduled_departure, flight_id) AS previous_destination FROM rotation_sample ORDER BY tail_number, scheduled_departure, flight_id

Look at the sample tables and SQL. Enter the rows you think the query returns, in this column order: flight_id, tail_number, previous_destination. Put each row in square brackets, then wrap all rows in another pair of brackets (JSON format). For example, [[1,null,null]] is one row with 3 values. Replace these with your predicted values. Use numbers without quotes, text in double quotes, and null for SQL NULL. Keep repeated rows. Use [] for no rows. Keep rows in the order specified by ORDER BY.

Showing the result or using a hint means a correct prediction counts as solved with help. Check prediction tests your answer without showing the result.

Hints and the answer are always available. Using them counts the question as solved with help.

Predict: the previous leg of the same aircraft — SQL practice · Runtime