Flight event data contains dep_time, carrier, origin, destination and tail_num (the plane that made the flight). The query below takes the flight event data and maps it into sessions of flight_date, carrier, and tail_num. For each session, a nested list of flight_legs by the aircraft on that day. The flight legs are numbered.
run: duckdb.table('../data/flights.parquet') extend { where: carrier = 'WN' and dep_time ? @2002-03-03 measure: flight_count is count() } -> { calculate: session_id is row_number() group_by: flight_date is dep_time.day carrier tail_num aggregate: flight_count max_delay is max(dep_delay) total_distance is distance.sum() nest: flight_legs is { order_by: 3 calculate: flght_leg is row_number() group_by: tail_num dep_minute is dep_time.minute origin destination dep_delay arr_delay } }
[ { "session_id": 1, "flight_date": "2002-03-03T00:00:00.000Z", "carrier": "WN", "tail_num": "N391SW", "flight_count": 5, "max_delay": 50, "total_distance": 4179, "flight_legs": [ { "flght_leg": 1, "tail_num": "N391SW", "dep_minute": "2002-03-03T07:50:00.000Z", "origin": "BWI", "destination": "PVD", "dep_delay": 0, "arr_delay": 0 }, { "flght_leg": 2, "tail_num": "N391SW", "dep_minute": "2002-03-03T09:44:00.000Z", "origin": "PVD", "destination": "TPA", "dep_delay": 14, "arr_delay": 19 }, { "flght_leg": 3, "tail_num": "N391SW", "dep_minute": "2002-03-03T13:50:00.000Z", "origin": "TPA", "destination": "PVD", "dep_delay": 35, "arr_delay": 48 }, { "flght_leg": 4, "tail_num": "N391SW", "dep_minute": "2002-03-03T17:08:00.000Z", "origin": "PVD", "destination": "BNA", "dep_delay": 38, "arr_delay": 61 }, { "flght_leg": 5, "tail_num": "N391SW", "dep_minute": "2002-03-03T19:40:00.000Z", "origin": "BNA", "destination": "HOU", "dep_delay": 50, "arr_delay": 60 } ] }, { "session_id": 2, "flight_date": "2002-03-03T00:00:00.000Z", "carrier": "WN", "tail_num": "N791SW", "flight_count": 5, "max_delay": 25, "total_distance": 4814, "flight_legs": [ { "flght_leg": 1, "tail_num": "N791SW", "dep_minute": "2002-03-03T08:00:00.000Z", "origin": "TPA", "destination": "LAS", "dep_delay": 0, "arr_delay": 12 }, { "flght_leg": 2, "tail_num": "N791SW", "dep_minute": "2002-03-03T10:37:00.000Z", "origin": "LAS", "destination": "MDW", "dep_delay": 17, "arr_delay": 22 }, { "flght_leg": 3, "tail_num": "N791SW", "dep_minute": "2002-03-03T16:45:00.000Z", "origin": "MDW", "destination": "STL", "dep_delay": 25, "arr_delay": 25 }, { "flght_leg": 4, "tail_num": "N791SW", "dep_minute": "2002-03-03T18:15:00.000Z", "origin": "STL", "destination": "MCO", "dep_delay": 20, "arr_delay": 10 }, { "flght_leg": 5, "tail_num": "N791SW", "dep_minute": "2002-03-03T22:05:00.000Z", "origin": "MCO", "destination": "FLL", "dep_delay": 20, "arr_delay": 20 } ] }, { "session_id": 4, "flight_date": "2002-03-03T00:00:00.000Z", "carrier": "WN", "tail_num": "N735SA", "flight_count": 9, "max_delay": 25, "total_distance": 3790, "flight_legs": [ { "flght_leg": 1, "tail_num": "N735SA", "dep_minute": "2002-03-03T07:15:00.000Z", "origin": "OAK", "destination": "LAS", "dep_delay": 0, "arr_delay": -10 }, { "flght_leg": 2, "tail_num": "N735SA", "dep_minute": "2002-03-03T09:00:00.000Z", "origin": "LAS", "destination": "SMF", "dep_delay": 0, "arr_delay": 5 }, { "flght_leg": 3, "tail_num": "N735SA", "dep_minute": "2002-03-03T11:00:00.000Z", "origin": "SMF", "destination": "SAN", "dep_delay": 15, "arr_delay": 10 }, { "flght_leg": 4, "tail_num": "N735SA", "dep_minute": "2002-03-03T12:45:00.000Z", "origin": "SAN", "destination": "LAS", "dep_delay": 20, "arr_delay": 17 }, { "flght_leg": 5, "tail_num": "N735SA", "dep_minute": "2002-03-03T14:14:00.000Z", "origin": "LAS", "destination": "SLC", "dep_delay": 19, "arr_delay": 19 }, { "flght_leg": 6, "tail_num": "N735SA", "dep_minute": "2002-03-03T16:55:00.000Z", "origin": "SLC", "destination": "PHX", "dep_delay": 25, "arr_delay": 10 }, { "flght_leg": 7, "tail_num": "N735SA", "dep_minute": "2002-03-03T18:53:00.000Z", "origin": "PHX", "destination": "SLC", "dep_delay": 18, "arr_delay": 10 }, { "flght_leg": 8, "tail_num": "N735SA", "dep_minute": "2002-03-03T20:45:00.000Z", "origin": "SLC", "destination": "RNO", "dep_delay": 15, "arr_delay": 10 }, { "flght_leg": 9, "tail_num": "N735SA", "dep_minute": "2002-03-03T21:27:00.000Z", "origin": "RNO", "destination": "PDX", "dep_delay": 12, "arr_delay": 5 } ] }, { "session_id": 6, "flight_date": "2002-03-03T00:00:00.000Z", "carrier": "WN", "tail_num": "N723SW", "flight_count": 4, "max_delay": 5, "total_distance": 5162, "flight_legs": [ { "flght_leg": 1, "tail_num": "N723SW", "dep_minute": "2002-03-03T08:40:00.000Z", "origin": "PHX", "destination": "PVD", "dep_delay": 0, "arr_delay": -16 }, { "flght_leg": 2, "tail_num": "N723SW", "dep_minute": "2002-03-03T15:55:00.000Z", "origin": "PVD", "destination": "PHX", "dep_delay": 0, "arr_delay": -30 }, { "flght_leg": 3, "tail_num": "N723SW", "dep_minute": "2002-03-03T20:20:00.000Z", "origin": "PHX", "destination": "SAN", "dep_delay": 5, "arr_delay": -12 }, { "flght_leg": 4, "tail_num": "N723SW", "dep_minute": "2002-03-03T21:00:00.000Z", "origin": "SAN", "destination": "PHX", "dep_delay": 0, "arr_delay": 0 } ] }, { "session_id": 3, "flight_date": "2002-03-03T00:00:00.000Z", "carrier": "WN", "tail_num": "N628SW", "flight_count": 7, "max_delay": 13, "total_distance": 2786, "flight_legs": [ { "flght_leg": 1, "tail_num": "N628SW", "dep_minute": "2002-03-03T08:00:00.000Z", "origin": "OAK", "destination": "LAX", "dep_delay": 0, "arr_delay": -7 }, { "flght_leg": 2, "tail_num": "N628SW", "dep_minute": "2002-03-03T10:00:00.000Z", "origin": "LAX", "destination": "OAK", "dep_delay": 0, "arr_delay": 5 }, { "flght_leg": 3, "tail_num": "N628SW", "dep_minute": "2002-03-03T11:45:00.000Z", "origin": "OAK", "destination": "GEG", "dep_delay": 10, "arr_delay": 0 }, { "flght_leg": 4, "tail_num": "N628SW", "dep_minute": "2002-03-03T13:55:00.000Z", "origin": "GEG", "destination": "SEA", "dep_delay": 0, "arr_delay": -5 }, { "flght_leg": 5, "tail_num": "N628SW", "dep_minute": "2002-03-03T15:25:00.000Z", "origin": "SEA", "destination": "RNO", "dep_delay": 0, "arr_delay": -12 }, { "flght_leg": 6, "tail_num": "N628SW", "dep_minute": "2002-03-03T17:30:00.000Z", "origin": "RNO", "destination": "LAS", "dep_delay": 0, "arr_delay": -10 }, { "flght_leg": 7, "tail_num": "N628SW", "dep_minute": "2002-03-03T19:23:00.000Z", "origin": "LAS", "destination": "PHX", "dep_delay": 13, "arr_delay": 20 } ] }, { "session_id": 5, "flight_date": "2002-03-03T00:00:00.000Z", "carrier": "WN", "tail_num": "N641SW", "flight_count": 7, "max_delay": 10, "total_distance": 3105, "flight_legs": [ { "flght_leg": 1, "tail_num": "N641SW", "dep_minute": "2002-03-03T09:15:00.000Z", "origin": "OAK", "destination": "ONT", "dep_delay": 0, "arr_delay": -1 }, { "flght_leg": 2, "tail_num": "N641SW", "dep_minute": "2002-03-03T10:50:00.000Z", "origin": "ONT", "destination": "OAK", "dep_delay": 0, "arr_delay": 0 }, { "flght_leg": 3, "tail_num": "N641SW", "dep_minute": "2002-03-03T12:35:00.000Z", "origin": "OAK", "destination": "PDX", "dep_delay": 10, "arr_delay": 5 }, { "flght_leg": 4, "tail_num": "N641SW", "dep_minute": "2002-03-03T14:25:00.000Z", "origin": "PDX", "destination": "OAK", "dep_delay": 0, "arr_delay": -3 }, { "flght_leg": 5, "tail_num": "N641SW", "dep_minute": "2002-03-03T16:37:00.000Z", "origin": "OAK", "destination": "LAX", "dep_delay": 7, "arr_delay": 0 }, { "flght_leg": 6, "tail_num": "N641SW", "dep_minute": "2002-03-03T18:15:00.000Z", "origin": "LAX", "destination": "ELP", "dep_delay": 0, "arr_delay": 7 }, { "flght_leg": 7, "tail_num": "N641SW", "dep_minute": "2002-03-03T21:30:00.000Z", "origin": "ELP", "destination": "MAF", "dep_delay": 0, "arr_delay": 0 } ] } ]
WITH __stage0 AS ( SELECT group_set, DATE_TRUNC('day', base."dep_time") as "flight_date__0", base."carrier" as "carrier__0", base."tail_num" as "tail_num__0", CASE WHEN group_set=0 THEN ROW_NUMBER() OVER(PARTITION BY group_set ORDER BY DATE_TRUNC('day', base."dep_time") desc NULLS LAST ) END as "session_id__0", CASE WHEN group_set=0 THEN COUNT(1) END as "flight_count__0", CASE WHEN group_set=0 THEN max(base."dep_delay") END as "max_delay__0", CASE WHEN group_set=0 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__0", CASE WHEN group_set=1 THEN base."tail_num" END as "tail_num__1", CASE WHEN group_set=1 THEN DATE_TRUNC('minute', base."dep_time") END as "dep_minute__1", CASE WHEN group_set=1 THEN base."origin" END as "origin__1", CASE WHEN group_set=1 THEN base."destination" END as "destination__1", CASE WHEN group_set=1 THEN base."dep_delay" END as "dep_delay__1", CASE WHEN group_set=1 THEN base."arr_delay" END as "arr_delay__1", CASE WHEN group_set=1 THEN ROW_NUMBER() OVER(PARTITION BY group_set, DATE_TRUNC('day', base."dep_time"), base."carrier", base."tail_num" ORDER BY CASE WHEN group_set=1 THEN DATE_TRUNC('minute', base."dep_time") END ASC NULLS LAST ) END as "flght_leg__1" FROM '../data/flights.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set WHERE (base."carrier"='WN') and ((base."dep_time">=TIMESTAMP '2002-03-03 00:00:00') and (base."dep_time"<TIMESTAMP '2002-03-04 00:00:00')) GROUP BY 1,2,3,4,9,10,11,12,13,14 ) SELECT MAX(CASE WHEN group_set=0 THEN "session_id__0" END) as "session_id", "flight_date__0" as "flight_date", "carrier__0" as "carrier", "tail_num__0" as "tail_num", MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count", MAX(CASE WHEN group_set=0 THEN "max_delay__0" END) as "max_delay", MAX(CASE WHEN group_set=0 THEN "total_distance__0" END) as "total_distance", COALESCE(LIST({ "flght_leg": "flght_leg__1", "tail_num": "tail_num__1", "dep_minute": "dep_minute__1", "origin": "origin__1", "destination": "destination__1", "dep_delay": "dep_delay__1", "arr_delay": "arr_delay__1"} ORDER BY "dep_minute__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "flight_legs" FROM __stage0 GROUP BY 2,3,4 ORDER BY 2 desc NULLS LAST