Malloy Documentation
search

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.

document
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
  } 
}
QUERY RESULTS
session_​idflight_​datecarriertail_​numflight_​countmax_​delaytotal_​distanceflight_​legs
32002-03-03WNN628SW7132,786
flght_​legtail_​numdep_​minuteorigindestinationdep_​delayarr_​delay
1N628SW2002-03-03 08:00OAKLAX0-7
2N628SW2002-03-03 10:00LAXOAK05
3N628SW2002-03-03 11:45OAKGEG100
4N628SW2002-03-03 13:55GEGSEA0-5
5N628SW2002-03-03 15:25SEARNO0-12
6N628SW2002-03-03 17:30RNOLAS0-10
7N628SW2002-03-03 19:23LASPHX1320
62002-03-03WNN723SW455,162
flght_​legtail_​numdep_​minuteorigindestinationdep_​delayarr_​delay
1N723SW2002-03-03 08:40PHXPVD0-16
2N723SW2002-03-03 15:55PVDPHX0-30
3N723SW2002-03-03 20:20PHXSAN5-12
4N723SW2002-03-03 21:00SANPHX00
22002-03-03WNN791SW5254,814
flght_​legtail_​numdep_​minuteorigindestinationdep_​delayarr_​delay
1N791SW2002-03-03 08:00TPALAS012
2N791SW2002-03-03 10:37LASMDW1722
3N791SW2002-03-03 16:45MDWSTL2525
4N791SW2002-03-03 18:15STLMCO2010
5N791SW2002-03-03 22:05MCOFLL2020
42002-03-03WNN735SA9253,790
flght_​legtail_​numdep_​minuteorigindestinationdep_​delayarr_​delay
1N735SA2002-03-03 07:15OAKLAS0-10
2N735SA2002-03-03 09:00LASSMF05
3N735SA2002-03-03 11:00SMFSAN1510
4N735SA2002-03-03 12:45SANLAS2017
5N735SA2002-03-03 14:14LASSLC1919
6N735SA2002-03-03 16:55SLCPHX2510
7N735SA2002-03-03 18:53PHXSLC1810
8N735SA2002-03-03 20:45SLCRNO1510
9N735SA2002-03-03 21:27RNOPDX125
52002-03-03WNN641SW7103,105
flght_​legtail_​numdep_​minuteorigindestinationdep_​delayarr_​delay
1N641SW2002-03-03 09:15OAKONT0-1
2N641SW2002-03-03 10:50ONTOAK00
3N641SW2002-03-03 12:35OAKPDX105
4N641SW2002-03-03 14:25PDXOAK0-3
5N641SW2002-03-03 16:37OAKLAX70
6N641SW2002-03-03 18:15LAXELP07
7N641SW2002-03-03 21:30ELPMAF00
12002-03-03WNN391SW5504,179
flght_​legtail_​numdep_​minuteorigindestinationdep_​delayarr_​delay
1N391SW2002-03-03 07:50BWIPVD00
2N391SW2002-03-03 09:44PVDTPA1419
3N391SW2002-03-03 13:50TPAPVD3548
4N391SW2002-03-03 17:08PVDBNA3861
5N391SW2002-03-03 19:40BNAHOU5060
[
  {
    "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": 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": 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": 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
      }
    ]
  },
  {
    "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
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    ROW_NUMBER() OVER(PARTITION BY group_set  ORDER BY  DATE_TRUNC('day', base."dep_time") desc NULLS LAST ) as "session_id__0",
    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
      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",
    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 ) as "flght_leg__1",
    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"
  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,3,4,5,10,11,12,13,14,15
)
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