What Is Malloy
Bar Charts
The examples below all use the following semantic model.
## renderer_next source: flights is duckdb.table('../data/flights.parquet') extend { where: dep_time = @2001 join_one: carriers is duckdb.table('../data/carriers.parquet') on carrier = carriers.code measure: flight_count is count() aircraft_count is count(tail_num) }
Line charts take two or three parameters.
First parameter - X-axis is time field or numeric expression
Second parameter - Y-axis is a numeric expression
Third (optional) Pivot is dimensional field (numeric or string)
Single line line_chart
# line_chart run: flights -> { group_by: departure_month is dep_time.month aggregate: flight_count order_by: departure_month }
[ { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 4071 }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 3672 }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 3869 }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 4328 }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 4407 }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 4451 }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 4440 }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 4607 }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 3616 }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 3809 }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 3819 }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 4086 } ]
SELECT DATE_TRUNC('month', base."dep_time") as "departure_month", COUNT(1) as "flight_count" FROM '../data/flights.parquet' as base WHERE (base."dep_time">=TIMESTAMP '2001-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2002-01-01 00:00:00') GROUP BY 1 ORDER BY 1 ASC NULLS LAST
Multi line line_chart
There are two modes to generate mulitple lines in a chart. You can group by an additional parameter (that shows the results as lines) or you can explicitly tag the y-axis parameters.
The third parameter in a line chart adds a line for each dimension found.
# line_chart run: flights -> { group_by: departure_month is dep_time.month aggregate: flight_count group_by: carriers.nickname order_by: departure_month }
[ { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 201, "nickname": "United" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 656, "nickname": "USAir" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 437, "nickname": "Delta" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 134, "nickname": "Continental" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 1315, "nickname": "Southwest" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 533, "nickname": "American" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 184, "nickname": "America West" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 493, "nickname": "Northwest" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 118, "nickname": "Alaska" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 387, "nickname": "Delta" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 92, "nickname": "Continental" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 1212, "nickname": "Southwest" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 154, "nickname": "America West" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 464, "nickname": "Northwest" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 443, "nickname": "American" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 138, "nickname": "Alaska" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 230, "nickname": "United" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 552, "nickname": "USAir" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 589, "nickname": "Delta" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 25, "nickname": "Continental" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 514, "nickname": "American" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 747, "nickname": "Southwest" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 150, "nickname": "America West" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 478, "nickname": "Northwest" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 127, "nickname": "Alaska" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 318, "nickname": "United" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 589, "nickname": "USAir" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 332, "nickname": "American Eagle" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 1218, "nickname": "Southwest" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 523, "nickname": "American" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 478, "nickname": "Northwest" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 120, "nickname": "Alaska" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 101, "nickname": "America West" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 553, "nickname": "Delta" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 112, "nickname": "Continental" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 641, "nickname": "USAir" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 305, "nickname": "American Eagle" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 277, "nickname": "United" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 535, "nickname": "Delta" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 124, "nickname": "Continental" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 1216, "nickname": "Southwest" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 466, "nickname": "American" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 151, "nickname": "America West" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 139, "nickname": "Alaska" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 491, "nickname": "Northwest" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 312, "nickname": "United" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 327, "nickname": "American Eagle" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 646, "nickname": "USAir" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 485, "nickname": "Delta" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 110, "nickname": "Continental" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 1254, "nickname": "Southwest" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 543, "nickname": "American" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 160, "nickname": "America West" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 512, "nickname": "Northwest" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 133, "nickname": "Alaska" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 338, "nickname": "United" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 309, "nickname": "American Eagle" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 607, "nickname": "USAir" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 272, "nickname": "United" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 367, "nickname": "American Eagle" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 583, "nickname": "USAir" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 498, "nickname": "Delta" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 123, "nickname": "Continental" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 1299, "nickname": "Southwest" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 522, "nickname": "American" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 142, "nickname": "America West" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 143, "nickname": "Alaska" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 491, "nickname": "Northwest" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 554, "nickname": "Delta" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 116, "nickname": "Continental" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 1318, "nickname": "Southwest" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 557, "nickname": "American" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 153, "nickname": "America West" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 516, "nickname": "Northwest" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 133, "nickname": "Alaska" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 363, "nickname": "United" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 554, "nickname": "USAir" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 343, "nickname": "American Eagle" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 1150, "nickname": "Southwest" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 347, "nickname": "American" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 419, "nickname": "Northwest" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 67, "nickname": "Alaska" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 135, "nickname": "America West" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 450, "nickname": "Delta" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 76, "nickname": "Continental" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 227, "nickname": "American Eagle" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 449, "nickname": "USAir" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 296, "nickname": "United" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 361, "nickname": "United" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 456, "nickname": "USAir" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 290, "nickname": "American Eagle" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 346, "nickname": "Delta" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 110, "nickname": "Continental" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 1309, "nickname": "Southwest" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 455, "nickname": "American" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 94, "nickname": "America West" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 98, "nickname": "Alaska" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 290, "nickname": "Northwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 212, "nickname": "American Eagle" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 499, "nickname": "USAir" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 399, "nickname": "United" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 1224, "nickname": "Southwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 462, "nickname": "American" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 52, "nickname": "America West" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 113, "nickname": "Alaska" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 317, "nickname": "Northwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 101, "nickname": "Continental" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 440, "nickname": "Delta" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 84, "nickname": "Continental" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 403, "nickname": "Delta" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 1159, "nickname": "Southwest" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 486, "nickname": "American" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 115, "nickname": "America West" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 432, "nickname": "Northwest" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 108, "nickname": "Alaska" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 418, "nickname": "United" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 247, "nickname": "American Eagle" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 634, "nickname": "USAir" } ]
SELECT DATE_TRUNC('month', base."dep_time") as "departure_month", COUNT(1) as "flight_count", carriers_0."nickname" as "nickname" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" WHERE (base."dep_time">=TIMESTAMP '2001-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2002-01-01 00:00:00') GROUP BY 1,3 ORDER BY 1 ASC NULLS LAST
Explicitly tagging the x and y axis.
# line_chart run: flights -> { group_by: # x departure_month is dep_time.month aggregate: # y south_west is flight_count {where: carriers.nickname = 'Southwest'} # y american is flight_count {where: carriers.nickname = 'American'} order_by: departure_month }
[ { "departure_month": "2001-01-01T00:00:00.000Z", "south_west": 1315, "american": 533 }, { "departure_month": "2001-02-01T00:00:00.000Z", "south_west": 1212, "american": 443 }, { "departure_month": "2001-03-01T00:00:00.000Z", "south_west": 747, "american": 514 }, { "departure_month": "2001-04-01T00:00:00.000Z", "south_west": 1218, "american": 523 }, { "departure_month": "2001-05-01T00:00:00.000Z", "south_west": 1216, "american": 466 }, { "departure_month": "2001-06-01T00:00:00.000Z", "south_west": 1254, "american": 543 }, { "departure_month": "2001-07-01T00:00:00.000Z", "south_west": 1299, "american": 522 }, { "departure_month": "2001-08-01T00:00:00.000Z", "south_west": 1318, "american": 557 }, { "departure_month": "2001-09-01T00:00:00.000Z", "south_west": 1150, "american": 347 }, { "departure_month": "2001-10-01T00:00:00.000Z", "south_west": 1309, "american": 455 }, { "departure_month": "2001-11-01T00:00:00.000Z", "south_west": 1224, "american": 462 }, { "departure_month": "2001-12-01T00:00:00.000Z", "south_west": 1159, "american": 486 } ]
SELECT DATE_TRUNC('month', base."dep_time") as "departure_month", (COUNT(CASE WHEN carriers_0."nickname"='Southwest' THEN 1 END)) as "south_west", (COUNT(CASE WHEN carriers_0."nickname"='American' THEN 1 END)) as "american" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" WHERE (base."dep_time">=TIMESTAMP '2001-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2002-01-01 00:00:00') GROUP BY 1 ORDER BY 1 ASC NULLS LAST
Line Charts nested in tables
When line charts are nested in tables the size is reduced. Nested line charts increase the density of information provided by the result.
run: flights -> { group_by: destination aggregate: flight_count # line_chart nest: by_month is { group_by: departure_month is dep_time.month aggregate: flight_count group_by: carriers.nickname order_by: departure_month } limit: 5 }
[ { "destination": "DFW", "flight_count": 2604, "by_month": [ { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 8, "nickname": "USAir" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 19, "nickname": "Delta" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 8, "nickname": "Continental" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 3, "nickname": "United" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 112, "nickname": "American" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 7, "nickname": "Northwest" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 2, "nickname": "America West" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 14, "nickname": "Delta" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 1, "nickname": "Continental" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 4, "nickname": "USAir" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 100, "nickname": "American" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 4, "nickname": "Northwest" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 2, "nickname": "America West" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 83, "nickname": "American Eagle" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 9, "nickname": "USAir" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 11, "nickname": "Delta" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 8, "nickname": "United" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 101, "nickname": "American" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 5, "nickname": "Northwest" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 2, "nickname": "America West" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 83, "nickname": "American Eagle" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 3, "nickname": "USAir" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 27, "nickname": "Delta" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 3, "nickname": "Continental" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 7, "nickname": "United" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 124, "nickname": "American" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 9, "nickname": "Northwest" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 2, "nickname": "America West" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 16, "nickname": "Delta" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 1, "nickname": "Continental" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 85, "nickname": "American Eagle" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 9, "nickname": "USAir" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 111, "nickname": "American" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 3, "nickname": "America West" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 3, "nickname": "Northwest" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 6, "nickname": "United" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 123, "nickname": "American" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 5, "nickname": "America West" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 9, "nickname": "Northwest" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 4, "nickname": "United" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 24, "nickname": "Delta" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 3, "nickname": "Continental" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 77, "nickname": "American Eagle" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 12, "nickname": "USAir" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 22, "nickname": "Delta" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 93, "nickname": "American Eagle" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 8, "nickname": "USAir" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 151, "nickname": "American" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 3, "nickname": "America West" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 8, "nickname": "Northwest" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 2, "nickname": "United" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 4, "nickname": "United" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 109, "nickname": "American" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 6, "nickname": "America West" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 13, "nickname": "Northwest" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 88, "nickname": "American Eagle" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 6, "nickname": "USAir" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 22, "nickname": "Delta" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 4, "nickname": "Continental" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 58, "nickname": "American Eagle" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 14, "nickname": "USAir" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 12, "nickname": "Delta" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 3, "nickname": "Continental" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 4, "nickname": "United" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 89, "nickname": "American" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 5, "nickname": "Northwest" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 2, "nickname": "America West" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 79, "nickname": "American Eagle" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 10, "nickname": "USAir" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 10, "nickname": "Delta" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 5, "nickname": "Continental" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 3, "nickname": "United" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 108, "nickname": "American" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 3, "nickname": "America West" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 4, "nickname": "Northwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 45, "nickname": "American Eagle" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 10, "nickname": "USAir" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 12, "nickname": "Delta" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 2, "nickname": "Continental" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 3, "nickname": "United" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 118, "nickname": "American" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 4, "nickname": "Northwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 1, "nickname": "America West" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 108, "nickname": "American" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 2, "nickname": "Northwest" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 2, "nickname": "United" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 14, "nickname": "Delta" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 3, "nickname": "Continental" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 68, "nickname": "American Eagle" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 12, "nickname": "USAir" } ] }, { "destination": "ATL", "flight_count": 2058, "by_month": [ { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 5, "nickname": "USAir" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 168, "nickname": "Delta" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 3, "nickname": "Continental" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 3, "nickname": "United" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 4, "nickname": "Northwest" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 4, "nickname": "American" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 103, "nickname": "Delta" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 3, "nickname": "Continental" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 4, "nickname": "USAir" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 11, "nickname": "Northwest" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 5, "nickname": "American" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 2, "nickname": "United" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 2, "nickname": "United" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 11, "nickname": "Northwest" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 4, "nickname": "American" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 9, "nickname": "USAir" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 175, "nickname": "Delta" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 8, "nickname": "Northwest" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 4, "nickname": "American" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 3, "nickname": "United" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 163, "nickname": "Delta" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 9, "nickname": "Continental" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 10, "nickname": "USAir" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 7, "nickname": "USAir" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 169, "nickname": "Delta" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 15, "nickname": "Continental" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 9, "nickname": "Northwest" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 1, "nickname": "American" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 142, "nickname": "Delta" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 15, "nickname": "Continental" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 4, "nickname": "USAir" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 10, "nickname": "Northwest" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 5, "nickname": "American" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 4, "nickname": "United" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 142, "nickname": "Delta" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 2, "nickname": "USAir" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 6, "nickname": "American" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 2, "nickname": "Northwest" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 1, "nickname": "United" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 173, "nickname": "Delta" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 8, "nickname": "Continental" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 3, "nickname": "USAir" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 5, "nickname": "Northwest" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 5, "nickname": "American" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 3, "nickname": "United" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 5, "nickname": "USAir" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 137, "nickname": "Delta" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 2, "nickname": "United" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 3, "nickname": "Northwest" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 3, "nickname": "American" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 6, "nickname": "United" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 2, "nickname": "American" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 2, "nickname": "Northwest" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 9, "nickname": "USAir" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 113, "nickname": "Delta" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 1, "nickname": "Continental" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 3, "nickname": "Northwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 2, "nickname": "American" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 5, "nickname": "United" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 140, "nickname": "Delta" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 10, "nickname": "Continental" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 3, "nickname": "USAir" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 15, "nickname": "USAir" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 135, "nickname": "Delta" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 9, "nickname": "United" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 5, "nickname": "Northwest" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 4, "nickname": "American" } ] }, { "destination": "PHX", "flight_count": 2054, "by_month": [ { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 82, "nickname": "Southwest" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 69, "nickname": "America West" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 2, "nickname": "Alaska" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 6, "nickname": "American" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 6, "nickname": "Northwest" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 2, "nickname": "USAir" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 100, "nickname": "Southwest" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 63, "nickname": "America West" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 8, "nickname": "Alaska" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 8, "nickname": "American" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 1, "nickname": "Northwest" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 2, "nickname": "United" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 2, "nickname": "Delta" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 5, "nickname": "USAir" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 4, "nickname": "USAir" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 3, "nickname": "Delta" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 1, "nickname": "United" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 59, "nickname": "Southwest" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 67, "nickname": "America West" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 11, "nickname": "Alaska" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 4, "nickname": "American" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 4, "nickname": "Northwest" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 40, "nickname": "America West" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 79, "nickname": "Southwest" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 5, "nickname": "Alaska" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 6, "nickname": "American" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 10, "nickname": "Northwest" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 2, "nickname": "Delta" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 4, "nickname": "USAir" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 1, "nickname": "United" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 62, "nickname": "America West" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 97, "nickname": "Southwest" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 5, "nickname": "Alaska" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 5, "nickname": "American" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 6, "nickname": "Northwest" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 5, "nickname": "USAir" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 1, "nickname": "Delta" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 2, "nickname": "Delta" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 8, "nickname": "USAir" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 102, "nickname": "Southwest" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 70, "nickname": "America West" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 6, "nickname": "Alaska" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 8, "nickname": "American" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 4, "nickname": "Northwest" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 100, "nickname": "Southwest" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 53, "nickname": "America West" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 1, "nickname": "Alaska" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 9, "nickname": "American" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 5, "nickname": "Northwest" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 9, "nickname": "USAir" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 2, "nickname": "Delta" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 6, "nickname": "USAir" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 101, "nickname": "Southwest" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 54, "nickname": "America West" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 6, "nickname": "Alaska" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 14, "nickname": "American" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 5, "nickname": "Northwest" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 6, "nickname": "United" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 5, "nickname": "USAir" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 1, "nickname": "Delta" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 3, "nickname": "United" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 98, "nickname": "Southwest" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 56, "nickname": "America West" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 3, "nickname": "Alaska" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 6, "nickname": "American" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 8, "nickname": "Northwest" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 4, "nickname": "United" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 92, "nickname": "Southwest" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 37, "nickname": "America West" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 3, "nickname": "Alaska" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 5, "nickname": "American" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 5, "nickname": "Northwest" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 7, "nickname": "USAir" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 3, "nickname": "Delta" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 1, "nickname": "Delta" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 94, "nickname": "Southwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 23, "nickname": "America West" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 8, "nickname": "Alaska" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 5, "nickname": "Northwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 2, "nickname": "American" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 2, "nickname": "United" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 3, "nickname": "USAir" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 39, "nickname": "America West" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 102, "nickname": "Southwest" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 13, "nickname": "Alaska" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 5, "nickname": "American" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 5, "nickname": "Northwest" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 8, "nickname": "United" } ] }, { "destination": "ORD", "flight_count": 1920, "by_month": [ { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 37, "nickname": "United" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 100, "nickname": "American" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 4, "nickname": "Northwest" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 1, "nickname": "America West" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 2, "nickname": "USAir" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 2, "nickname": "Continental" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 1, "nickname": "Delta" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 2, "nickname": "Continental" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 3, "nickname": "USAir" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 84, "nickname": "American" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 7, "nickname": "Northwest" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 1, "nickname": "America West" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 41, "nickname": "United" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 1, "nickname": "Continental" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 1, "nickname": "Delta" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 9, "nickname": "USAir" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 99, "nickname": "American" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 9, "nickname": "Northwest" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 1, "nickname": "America West" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 63, "nickname": "United" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 52, "nickname": "United" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 59, "nickname": "American" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 10, "nickname": "Northwest" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 13, "nickname": "USAir" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 64, "nickname": "United" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 66, "nickname": "American" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 2, "nickname": "Northwest" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 6, "nickname": "USAir" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 3, "nickname": "Continental" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 70, "nickname": "United" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 89, "nickname": "American" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 12, "nickname": "Northwest" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 5, "nickname": "USAir" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 57, "nickname": "American" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 3, "nickname": "Northwest" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 67, "nickname": "United" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 5, "nickname": "Continental" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 4, "nickname": "Delta" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 7, "nickname": "USAir" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 4, "nickname": "USAir" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 3, "nickname": "Delta" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 3, "nickname": "Continental" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 58, "nickname": "United" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 117, "nickname": "American" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 9, "nickname": "Northwest" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 47, "nickname": "American" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 4, "nickname": "Northwest" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 60, "nickname": "United" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 1, "nickname": "Continental" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 1, "nickname": "Delta" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 6, "nickname": "USAir" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 51, "nickname": "American" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 1, "nickname": "Northwest" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 80, "nickname": "United" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 1, "nickname": "Delta" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 4, "nickname": "USAir" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 60, "nickname": "American" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 8, "nickname": "Northwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 97, "nickname": "United" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 4, "nickname": "Continental" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 12, "nickname": "USAir" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 15, "nickname": "USAir" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 1, "nickname": "Delta" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 1, "nickname": "Continental" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 109, "nickname": "United" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 95, "nickname": "American" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 6, "nickname": "Northwest" } ] }, { "destination": "LAX", "flight_count": 1812, "by_month": [ { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 8, "nickname": "Northwest" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 63, "nickname": "Southwest" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 30, "nickname": "American" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 19, "nickname": "Alaska" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 8, "nickname": "America West" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 30, "nickname": "United" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 3, "nickname": "Delta" }, { "departure_month": "2001-01-01T00:00:00.000Z", "flight_count": 8, "nickname": "USAir" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 2, "nickname": "Delta" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 8, "nickname": "USAir" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 11, "nickname": "Northwest" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 78, "nickname": "Southwest" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 33, "nickname": "American" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 10, "nickname": "Alaska" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 4, "nickname": "America West" }, { "departure_month": "2001-02-01T00:00:00.000Z", "flight_count": 16, "nickname": "United" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 25, "nickname": "United" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 6, "nickname": "Northwest" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 44, "nickname": "Southwest" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 50, "nickname": "American" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 12, "nickname": "Alaska" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 4, "nickname": "America West" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 1, "nickname": "USAir" }, { "departure_month": "2001-03-01T00:00:00.000Z", "flight_count": 2, "nickname": "Delta" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 4, "nickname": "Delta" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 2, "nickname": "USAir" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 10, "nickname": "Northwest" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 50, "nickname": "American" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 61, "nickname": "Southwest" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 17, "nickname": "Alaska" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 1, "nickname": "America West" }, { "departure_month": "2001-04-01T00:00:00.000Z", "flight_count": 26, "nickname": "United" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 11, "nickname": "Northwest" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 45, "nickname": "American" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 56, "nickname": "Southwest" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 13, "nickname": "Alaska" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 6, "nickname": "America West" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 29, "nickname": "United" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 2, "nickname": "Delta" }, { "departure_month": "2001-05-01T00:00:00.000Z", "flight_count": 1, "nickname": "USAir" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 2, "nickname": "Delta" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 1, "nickname": "USAir" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 10, "nickname": "Northwest" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 55, "nickname": "Southwest" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 57, "nickname": "American" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 5, "nickname": "Alaska" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 5, "nickname": "America West" }, { "departure_month": "2001-06-01T00:00:00.000Z", "flight_count": 25, "nickname": "United" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 1, "nickname": "USAir" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 4, "nickname": "Delta" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 17, "nickname": "United" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 12, "nickname": "Northwest" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 30, "nickname": "American" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 52, "nickname": "Southwest" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 13, "nickname": "Alaska" }, { "departure_month": "2001-07-01T00:00:00.000Z", "flight_count": 2, "nickname": "America West" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 6, "nickname": "Delta" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 11, "nickname": "Northwest" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 43, "nickname": "American" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 60, "nickname": "Southwest" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 12, "nickname": "Alaska" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 6, "nickname": "America West" }, { "departure_month": "2001-08-01T00:00:00.000Z", "flight_count": 29, "nickname": "United" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 11, "nickname": "Northwest" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 64, "nickname": "Southwest" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 20, "nickname": "American" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 7, "nickname": "Alaska" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 3, "nickname": "America West" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 28, "nickname": "United" }, { "departure_month": "2001-09-01T00:00:00.000Z", "flight_count": 1, "nickname": "USAir" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 33, "nickname": "United" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 6, "nickname": "Northwest" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 21, "nickname": "American" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 65, "nickname": "Southwest" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 12, "nickname": "Alaska" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 2, "nickname": "America West" }, { "departure_month": "2001-10-01T00:00:00.000Z", "flight_count": 1, "nickname": "Delta" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 3, "nickname": "Delta" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 3, "nickname": "USAir" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 11, "nickname": "Northwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 24, "nickname": "American" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 52, "nickname": "Southwest" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 10, "nickname": "Alaska" }, { "departure_month": "2001-11-01T00:00:00.000Z", "flight_count": 24, "nickname": "United" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 10, "nickname": "Northwest" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 81, "nickname": "Southwest" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 23, "nickname": "American" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 6, "nickname": "Alaska" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 5, "nickname": "America West" }, { "departure_month": "2001-12-01T00:00:00.000Z", "flight_count": 19, "nickname": "United" } ] } ]
WITH __stage0 AS ( SELECT group_set, base."destination" as "destination__0", CASE WHEN group_set=0 THEN COUNT(1) END as "flight_count__0", CASE WHEN group_set=1 THEN DATE_TRUNC('month', base."dep_time") END as "departure_month__1", CASE WHEN group_set=1 THEN carriers_0."nickname" END as "nickname__1", CASE WHEN group_set=1 THEN COUNT(1) END as "flight_count__1" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set WHERE (base."dep_time">=TIMESTAMP '2001-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2002-01-01 00:00:00') GROUP BY 1,2,4,5 ) SELECT "destination__0" as "destination", MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count", COALESCE(LIST({ "departure_month": "departure_month__1", "flight_count": "flight_count__1", "nickname": "nickname__1"} ORDER BY "departure_month__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_month" FROM __stage0 GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 5