What Is Malloy
Lists
Malloy can create simple bar charts and bar charts with series breakdowns, either stacked or grouped.
The examples below all use the following semantic model.
source: flights is duckdb.table('../data/flights.parquet') extend { join_one: carriers is duckdb.table('../data/carriers.parquet') on carrier = carriers.code measure: flight_count is count() to_sfo_flight_count is flight_count { where: destination = 'SFO' } to_oak_flight_count is flight_count { where: destination = 'OAK' } from_sfo_flight_count is flight_count { where: origin = 'SFO' } from_oak_flight_count is flight_count { where: origin = 'OAK' } aircraft_count is count(tail_num) }
Simple Bar Chart
Basic bar charts use the first dimension as the X-axis, and the first aggregate as the Y-axis:
# bar_chart run: flights -> { group_by: carriers.nickname aggregate: flight_count }
[ { "nickname": "Southwest", "flight_count": 88751 }, { "nickname": "USAir", "flight_count": 37683 }, { "nickname": "American", "flight_count": 34577 }, { "nickname": "Northwest", "flight_count": 33580 }, { "nickname": "United", "flight_count": 32757 }, { "nickname": "Delta", "flight_count": 32130 }, { "nickname": "Continental Express", "flight_count": 16074 }, { "nickname": "American Eagle", "flight_count": 15869 }, { "nickname": "Atlantic Southeast", "flight_count": 15769 }, { "nickname": "America West", "flight_count": 9750 }, { "nickname": "Alaska", "flight_count": 8453 }, { "nickname": "Continental", "flight_count": 7139 }, { "nickname": "Jetblue", "flight_count": 4842 }, { "nickname": "Comair", "flight_count": 4420 }, { "nickname": "ATA", "flight_count": 3033 } ]
SELECT carriers_0."nickname" as "nickname", COUNT(1) as "flight_count" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" GROUP BY 1 ORDER BY 2 desc NULLS LAST
Bar Chart with Breakdown
Adding a second dimension to the query in the group_by: clause creates a bar chart with a breakdown along that second dimension:
# bar_chart run: flights -> { where: destination ? 'SFO' | 'OAK' | 'SJC' group_by: carriers.nickname, destination aggregate: flight_count }
[ { "nickname": "Southwest", "destination": "OAK", "flight_count": 3967 }, { "nickname": "United", "destination": "SFO", "flight_count": 2432 }, { "nickname": "Southwest", "destination": "SJC", "flight_count": 2143 }, { "nickname": "American", "destination": "SFO", "flight_count": 686 }, { "nickname": "American", "destination": "SJC", "flight_count": 558 }, { "nickname": "Northwest", "destination": "SFO", "flight_count": 520 }, { "nickname": "Alaska", "destination": "SJC", "flight_count": 367 }, { "nickname": "United", "destination": "SJC", "flight_count": 318 }, { "nickname": "Jetblue", "destination": "OAK", "flight_count": 297 }, { "nickname": "United", "destination": "OAK", "flight_count": 249 }, { "nickname": "Alaska", "destination": "OAK", "flight_count": 248 }, { "nickname": "Alaska", "destination": "SFO", "flight_count": 223 }, { "nickname": "America West", "destination": "SFO", "flight_count": 209 }, { "nickname": "America West", "destination": "SJC", "flight_count": 180 }, { "nickname": "Northwest", "destination": "SJC", "flight_count": 168 }, { "nickname": "America West", "destination": "OAK", "flight_count": 162 }, { "nickname": "USAir", "destination": "SFO", "flight_count": 156 }, { "nickname": "American", "destination": "OAK", "flight_count": 141 }, { "nickname": "Delta", "destination": "SFO", "flight_count": 132 }, { "nickname": "Southwest", "destination": "SFO", "flight_count": 86 }, { "nickname": "ATA", "destination": "SFO", "flight_count": 37 }, { "nickname": "Continental", "destination": "SJC", "flight_count": 35 }, { "nickname": "Jetblue", "destination": "SJC", "flight_count": 21 }, { "nickname": "Continental", "destination": "SFO", "flight_count": 21 }, { "nickname": "Delta", "destination": "SJC", "flight_count": 19 }, { "nickname": "Delta", "destination": "OAK", "flight_count": 10 }, { "nickname": "ATA", "destination": "SJC", "flight_count": 9 }, { "nickname": "Continental", "destination": "OAK", "flight_count": 8 } ]
SELECT carriers_0."nickname" as "nickname", base."destination" as "destination", COUNT(1) as "flight_count" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" WHERE (base."destination"='SFO') or ((base."destination"='OAK') or (base."destination"='SJC')) GROUP BY 1,2 ORDER BY 3 desc NULLS LAST
# bar_chart run: flights -> { where: destination ? 'SFO' | 'OAK' | 'SJC' group_by: destination, carriers.nickname aggregate: flight_count }
[ { "destination": "OAK", "nickname": "Southwest", "flight_count": 3967 }, { "destination": "SFO", "nickname": "United", "flight_count": 2432 }, { "destination": "SJC", "nickname": "Southwest", "flight_count": 2143 }, { "destination": "SFO", "nickname": "American", "flight_count": 686 }, { "destination": "SJC", "nickname": "American", "flight_count": 558 }, { "destination": "SFO", "nickname": "Northwest", "flight_count": 520 }, { "destination": "SJC", "nickname": "Alaska", "flight_count": 367 }, { "destination": "SJC", "nickname": "United", "flight_count": 318 }, { "destination": "OAK", "nickname": "Jetblue", "flight_count": 297 }, { "destination": "OAK", "nickname": "United", "flight_count": 249 }, { "destination": "OAK", "nickname": "Alaska", "flight_count": 248 }, { "destination": "SFO", "nickname": "Alaska", "flight_count": 223 }, { "destination": "SFO", "nickname": "America West", "flight_count": 209 }, { "destination": "SJC", "nickname": "America West", "flight_count": 180 }, { "destination": "SJC", "nickname": "Northwest", "flight_count": 168 }, { "destination": "OAK", "nickname": "America West", "flight_count": 162 }, { "destination": "SFO", "nickname": "USAir", "flight_count": 156 }, { "destination": "OAK", "nickname": "American", "flight_count": 141 }, { "destination": "SFO", "nickname": "Delta", "flight_count": 132 }, { "destination": "SFO", "nickname": "Southwest", "flight_count": 86 }, { "destination": "SFO", "nickname": "ATA", "flight_count": 37 }, { "destination": "SJC", "nickname": "Continental", "flight_count": 35 }, { "destination": "SJC", "nickname": "Jetblue", "flight_count": 21 }, { "destination": "SFO", "nickname": "Continental", "flight_count": 21 }, { "destination": "SJC", "nickname": "Delta", "flight_count": 19 }, { "destination": "OAK", "nickname": "Delta", "flight_count": 10 }, { "destination": "SJC", "nickname": "ATA", "flight_count": 9 }, { "destination": "OAK", "nickname": "Continental", "flight_count": 8 } ]
SELECT base."destination" as "destination", carriers_0."nickname" as "nickname", COUNT(1) as "flight_count" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" WHERE (base."destination"='SFO') or ((base."destination"='OAK') or (base."destination"='SJC')) GROUP BY 1,2 ORDER BY 3 desc NULLS LAST
To stack bars instead of grouping them, add the stack tag:
# bar_chart.stack run: flights -> { where: destination ? 'SFO' | 'OAK' | 'SJC' group_by: carriers.nickname, destination aggregate: flight_count }
[ { "nickname": "Southwest", "destination": "OAK", "flight_count": 3967 }, { "nickname": "United", "destination": "SFO", "flight_count": 2432 }, { "nickname": "Southwest", "destination": "SJC", "flight_count": 2143 }, { "nickname": "American", "destination": "SFO", "flight_count": 686 }, { "nickname": "American", "destination": "SJC", "flight_count": 558 }, { "nickname": "Northwest", "destination": "SFO", "flight_count": 520 }, { "nickname": "Alaska", "destination": "SJC", "flight_count": 367 }, { "nickname": "United", "destination": "SJC", "flight_count": 318 }, { "nickname": "Jetblue", "destination": "OAK", "flight_count": 297 }, { "nickname": "United", "destination": "OAK", "flight_count": 249 }, { "nickname": "Alaska", "destination": "OAK", "flight_count": 248 }, { "nickname": "Alaska", "destination": "SFO", "flight_count": 223 }, { "nickname": "America West", "destination": "SFO", "flight_count": 209 }, { "nickname": "America West", "destination": "SJC", "flight_count": 180 }, { "nickname": "Northwest", "destination": "SJC", "flight_count": 168 }, { "nickname": "America West", "destination": "OAK", "flight_count": 162 }, { "nickname": "USAir", "destination": "SFO", "flight_count": 156 }, { "nickname": "American", "destination": "OAK", "flight_count": 141 }, { "nickname": "Delta", "destination": "SFO", "flight_count": 132 }, { "nickname": "Southwest", "destination": "SFO", "flight_count": 86 }, { "nickname": "ATA", "destination": "SFO", "flight_count": 37 }, { "nickname": "Continental", "destination": "SJC", "flight_count": 35 }, { "nickname": "Jetblue", "destination": "SJC", "flight_count": 21 }, { "nickname": "Continental", "destination": "SFO", "flight_count": 21 }, { "nickname": "Delta", "destination": "SJC", "flight_count": 19 }, { "nickname": "Delta", "destination": "OAK", "flight_count": 10 }, { "nickname": "ATA", "destination": "SJC", "flight_count": 9 }, { "nickname": "Continental", "destination": "OAK", "flight_count": 8 } ]
SELECT carriers_0."nickname" as "nickname", base."destination" as "destination", COUNT(1) as "flight_count" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" WHERE (base."destination"='SFO') or ((base."destination"='OAK') or (base."destination"='SJC')) GROUP BY 1,2 ORDER BY 3 desc NULLS LAST
Controlling Behavior with Tag Parameters
You can explicitly set any of the parameters in the bar_chart tag. The following parameters are supported:
x
y
series
# bar_chart { x=nickname series=destination y=flight_count } run: flights -> { where: destination ? 'SFO' | 'OAK' | 'SJC' group_by: destination, carriers.nickname aggregate: flight_count }
[ { "destination": "OAK", "nickname": "Southwest", "flight_count": 3967 }, { "destination": "SFO", "nickname": "United", "flight_count": 2432 }, { "destination": "SJC", "nickname": "Southwest", "flight_count": 2143 }, { "destination": "SFO", "nickname": "American", "flight_count": 686 }, { "destination": "SJC", "nickname": "American", "flight_count": 558 }, { "destination": "SFO", "nickname": "Northwest", "flight_count": 520 }, { "destination": "SJC", "nickname": "Alaska", "flight_count": 367 }, { "destination": "SJC", "nickname": "United", "flight_count": 318 }, { "destination": "OAK", "nickname": "Jetblue", "flight_count": 297 }, { "destination": "OAK", "nickname": "United", "flight_count": 249 }, { "destination": "OAK", "nickname": "Alaska", "flight_count": 248 }, { "destination": "SFO", "nickname": "Alaska", "flight_count": 223 }, { "destination": "SFO", "nickname": "America West", "flight_count": 209 }, { "destination": "SJC", "nickname": "America West", "flight_count": 180 }, { "destination": "SJC", "nickname": "Northwest", "flight_count": 168 }, { "destination": "OAK", "nickname": "America West", "flight_count": 162 }, { "destination": "SFO", "nickname": "USAir", "flight_count": 156 }, { "destination": "OAK", "nickname": "American", "flight_count": 141 }, { "destination": "SFO", "nickname": "Delta", "flight_count": 132 }, { "destination": "SFO", "nickname": "Southwest", "flight_count": 86 }, { "destination": "SFO", "nickname": "ATA", "flight_count": 37 }, { "destination": "SJC", "nickname": "Continental", "flight_count": 35 }, { "destination": "SJC", "nickname": "Jetblue", "flight_count": 21 }, { "destination": "SFO", "nickname": "Continental", "flight_count": 21 }, { "destination": "SJC", "nickname": "Delta", "flight_count": 19 }, { "destination": "OAK", "nickname": "Delta", "flight_count": 10 }, { "destination": "SJC", "nickname": "ATA", "flight_count": 9 }, { "destination": "OAK", "nickname": "Continental", "flight_count": 8 } ]
SELECT base."destination" as "destination", carriers_0."nickname" as "nickname", COUNT(1) as "flight_count" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" WHERE (base."destination"='SFO') or ((base."destination"='OAK') or (base."destination"='SJC')) GROUP BY 1,2 ORDER BY 3 desc NULLS LAST
Breakdown Chart with Multiple Measures
To create a breakdown chart with multiple measures as the series, provide y as an array of field names.
# bar_chart { x=nickname y=['to_sfo_flight_count', 'to_oak_flight_count'] } run: flights -> { group_by: carriers.nickname aggregate: to_sfo_flight_count to_oak_flight_count }
[ { "nickname": "United", "to_sfo_flight_count": 2432, "to_oak_flight_count": 249 }, { "nickname": "American", "to_sfo_flight_count": 686, "to_oak_flight_count": 141 }, { "nickname": "Northwest", "to_sfo_flight_count": 520, "to_oak_flight_count": 0 }, { "nickname": "Alaska", "to_sfo_flight_count": 223, "to_oak_flight_count": 248 }, { "nickname": "America West", "to_sfo_flight_count": 209, "to_oak_flight_count": 162 }, { "nickname": "USAir", "to_sfo_flight_count": 156, "to_oak_flight_count": 0 }, { "nickname": "Delta", "to_sfo_flight_count": 132, "to_oak_flight_count": 10 }, { "nickname": "Southwest", "to_sfo_flight_count": 86, "to_oak_flight_count": 3967 }, { "nickname": "ATA", "to_sfo_flight_count": 37, "to_oak_flight_count": 0 }, { "nickname": "Continental", "to_sfo_flight_count": 21, "to_oak_flight_count": 8 }, { "nickname": "Continental Express", "to_sfo_flight_count": 0, "to_oak_flight_count": 0 }, { "nickname": "American Eagle", "to_sfo_flight_count": 0, "to_oak_flight_count": 0 }, { "nickname": "Comair", "to_sfo_flight_count": 0, "to_oak_flight_count": 0 }, { "nickname": "Atlantic Southeast", "to_sfo_flight_count": 0, "to_oak_flight_count": 0 }, { "nickname": "Jetblue", "to_sfo_flight_count": 0, "to_oak_flight_count": 297 } ]
SELECT carriers_0."nickname" as "nickname", (COUNT(CASE WHEN base."destination"='SFO' THEN 1 END)) as "to_sfo_flight_count", (COUNT(CASE WHEN base."destination"='OAK' THEN 1 END)) as "to_oak_flight_count" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" GROUP BY 1 ORDER BY 2 desc NULLS LAST
Embedded Parameters
Parameters can also be embedded directly in a query by tagging fields:
# bar_chart run: flights -> { where: destination ? 'SFO' | 'OAK' | 'SJC' group_by: # series destination # x carriers.nickname aggregate: # y flight_count aircraft_count }
[ { "destination": "OAK", "nickname": "Southwest", "flight_count": 3967, "aircraft_count": 8 }, { "destination": "SFO", "nickname": "United", "flight_count": 2432, "aircraft_count": 6 }, { "destination": "SJC", "nickname": "Southwest", "flight_count": 2143, "aircraft_count": 8 }, { "destination": "SFO", "nickname": "American", "flight_count": 686, "aircraft_count": 6 }, { "destination": "SJC", "nickname": "American", "flight_count": 558, "aircraft_count": 5 }, { "destination": "SFO", "nickname": "Northwest", "flight_count": 520, "aircraft_count": 5 }, { "destination": "SJC", "nickname": "Alaska", "flight_count": 367, "aircraft_count": 1 }, { "destination": "SJC", "nickname": "United", "flight_count": 318, "aircraft_count": 5 }, { "destination": "OAK", "nickname": "Jetblue", "flight_count": 297, "aircraft_count": 2 }, { "destination": "OAK", "nickname": "United", "flight_count": 249, "aircraft_count": 5 }, { "destination": "OAK", "nickname": "Alaska", "flight_count": 248, "aircraft_count": 1 }, { "destination": "SFO", "nickname": "Alaska", "flight_count": 223, "aircraft_count": 1 }, { "destination": "SFO", "nickname": "America West", "flight_count": 209, "aircraft_count": 1 }, { "destination": "SJC", "nickname": "America West", "flight_count": 180, "aircraft_count": 1 }, { "destination": "SJC", "nickname": "Northwest", "flight_count": 168, "aircraft_count": 3 }, { "destination": "OAK", "nickname": "America West", "flight_count": 162, "aircraft_count": 1 }, { "destination": "SFO", "nickname": "USAir", "flight_count": 156, "aircraft_count": 2 }, { "destination": "OAK", "nickname": "American", "flight_count": 141, "aircraft_count": 4 }, { "destination": "SFO", "nickname": "Delta", "flight_count": 132, "aircraft_count": 3 }, { "destination": "SFO", "nickname": "Southwest", "flight_count": 86, "aircraft_count": 6 }, { "destination": "SFO", "nickname": "ATA", "flight_count": 37, "aircraft_count": 1 }, { "destination": "SJC", "nickname": "Continental", "flight_count": 35, "aircraft_count": 1 }, { "destination": "SJC", "nickname": "Jetblue", "flight_count": 21, "aircraft_count": 2 }, { "destination": "SFO", "nickname": "Continental", "flight_count": 21, "aircraft_count": 1 }, { "destination": "SJC", "nickname": "Delta", "flight_count": 19, "aircraft_count": 1 }, { "destination": "OAK", "nickname": "Delta", "flight_count": 10, "aircraft_count": 1 }, { "destination": "SJC", "nickname": "ATA", "flight_count": 9, "aircraft_count": 1 }, { "destination": "OAK", "nickname": "Continental", "flight_count": 8, "aircraft_count": 1 } ]
SELECT base."destination" as "destination", carriers_0."nickname" as "nickname", COUNT(1) as "flight_count", count(distinct base."tail_num") as "aircraft_count" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" WHERE (base."destination"='SFO') or ((base."destination"='OAK') or (base."destination"='SJC')) GROUP BY 1,2 ORDER BY 3 desc NULLS LAST
For measure series, tag multiple aggregates as y by placing the tag above the aggregate: keyword:
# bar_chart run: flights -> { group_by: carriers.nickname # y aggregate: to_sfo_flight_count to_oak_flight_count }
[ { "nickname": "United", "to_sfo_flight_count": 2432, "to_oak_flight_count": 249 }, { "nickname": "American", "to_sfo_flight_count": 686, "to_oak_flight_count": 141 }, { "nickname": "Northwest", "to_sfo_flight_count": 520, "to_oak_flight_count": 0 }, { "nickname": "Alaska", "to_sfo_flight_count": 223, "to_oak_flight_count": 248 }, { "nickname": "America West", "to_sfo_flight_count": 209, "to_oak_flight_count": 162 }, { "nickname": "USAir", "to_sfo_flight_count": 156, "to_oak_flight_count": 0 }, { "nickname": "Delta", "to_sfo_flight_count": 132, "to_oak_flight_count": 10 }, { "nickname": "Southwest", "to_sfo_flight_count": 86, "to_oak_flight_count": 3967 }, { "nickname": "ATA", "to_sfo_flight_count": 37, "to_oak_flight_count": 0 }, { "nickname": "Continental", "to_sfo_flight_count": 21, "to_oak_flight_count": 8 }, { "nickname": "Continental Express", "to_sfo_flight_count": 0, "to_oak_flight_count": 0 }, { "nickname": "American Eagle", "to_sfo_flight_count": 0, "to_oak_flight_count": 0 }, { "nickname": "Comair", "to_sfo_flight_count": 0, "to_oak_flight_count": 0 }, { "nickname": "Atlantic Southeast", "to_sfo_flight_count": 0, "to_oak_flight_count": 0 }, { "nickname": "Jetblue", "to_sfo_flight_count": 0, "to_oak_flight_count": 297 } ]
SELECT carriers_0."nickname" as "nickname", (COUNT(CASE WHEN base."destination"='SFO' THEN 1 END)) as "to_sfo_flight_count", (COUNT(CASE WHEN base."destination"='OAK' THEN 1 END)) as "to_oak_flight_count" FROM '../data/flights.parquet' as base LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON base."carrier"=carriers_0."code" GROUP BY 1 ORDER BY 2 desc NULLS LAST
Nested Bar Charts
Bar charts can be used inside of nested queries:
run: flights -> { group_by: carriers.nickname aggregate: flight_count # bar_chart.stack nest: top_destinations is { group_by: destination # y aggregate: from_sfo_flight_count from_oak_flight_count limit: 10 } # bar_chart nest: by_hour_of_day is { group_by: hour_of_day is hour(dep_time) aggregate: flight_count } }
[ { "nickname": "Southwest", "flight_count": 88751, "top_destinations": [ { "destination": "SAN", "from_sfo_flight_count": 45, "from_oak_flight_count": 479 }, { "destination": "PHX", "from_sfo_flight_count": 41, "from_oak_flight_count": 167 }, { "destination": "BHM", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BUF", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "GEG", "from_sfo_flight_count": 0, "from_oak_flight_count": 32 }, { "destination": "LAX", "from_sfo_flight_count": 0, "from_oak_flight_count": 745 }, { "destination": "MAF", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "OAK", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ORF", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "SAT", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 7, "flight_count": 6874 }, { "hour_of_day": 8, "flight_count": 6229 }, { "hour_of_day": 17, "flight_count": 6058 }, { "hour_of_day": 18, "flight_count": 5688 }, { "hour_of_day": 9, "flight_count": 5623 }, { "hour_of_day": 16, "flight_count": 5598 }, { "hour_of_day": 12, "flight_count": 5552 }, { "hour_of_day": 11, "flight_count": 5538 }, { "hour_of_day": 19, "flight_count": 5433 }, { "hour_of_day": 13, "flight_count": 5420 }, { "hour_of_day": 14, "flight_count": 5319 }, { "hour_of_day": 10, "flight_count": 5291 }, { "hour_of_day": 15, "flight_count": 5266 }, { "hour_of_day": 20, "flight_count": 4907 }, { "hour_of_day": 6, "flight_count": 4421 }, { "hour_of_day": 21, "flight_count": 3741 }, { "hour_of_day": 22, "flight_count": 1335 }, { "hour_of_day": 23, "flight_count": 229 }, { "hour_of_day": 5, "flight_count": 175 }, { "hour_of_day": 0, "flight_count": 36 }, { "hour_of_day": 1, "flight_count": 14 }, { "hour_of_day": 2, "flight_count": 3 }, { "hour_of_day": 3, "flight_count": 1 } ] }, { "nickname": "USAir", "flight_count": 37683, "top_destinations": [ { "destination": "CLT", "from_sfo_flight_count": 49, "from_oak_flight_count": 0 }, { "destination": "PHL", "from_sfo_flight_count": 44, "from_oak_flight_count": 0 }, { "destination": "PIT", "from_sfo_flight_count": 40, "from_oak_flight_count": 0 }, { "destination": "BWI", "from_sfo_flight_count": 24, "from_oak_flight_count": 0 }, { "destination": "ABE", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ATL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "AVL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "AVP", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BOS", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BTV", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 15, "flight_count": 2718 }, { "hour_of_day": 7, "flight_count": 2667 }, { "hour_of_day": 17, "flight_count": 2608 }, { "hour_of_day": 11, "flight_count": 2477 }, { "hour_of_day": 8, "flight_count": 2455 }, { "hour_of_day": 13, "flight_count": 2362 }, { "hour_of_day": 18, "flight_count": 2310 }, { "hour_of_day": 9, "flight_count": 2307 }, { "hour_of_day": 6, "flight_count": 2262 }, { "hour_of_day": 16, "flight_count": 2147 }, { "hour_of_day": 10, "flight_count": 2142 }, { "hour_of_day": 12, "flight_count": 2140 }, { "hour_of_day": 14, "flight_count": 1980 }, { "hour_of_day": 19, "flight_count": 1972 }, { "hour_of_day": 20, "flight_count": 1800 }, { "hour_of_day": 21, "flight_count": 1219 }, { "hour_of_day": 22, "flight_count": 1108 }, { "hour_of_day": 5, "flight_count": 621 }, { "hour_of_day": 23, "flight_count": 276 }, { "hour_of_day": 0, "flight_count": 61 }, { "hour_of_day": 1, "flight_count": 32 }, { "hour_of_day": 2, "flight_count": 9 }, { "hour_of_day": 4, "flight_count": 8 }, { "hour_of_day": 3, "flight_count": 2 } ] }, { "nickname": "American", "flight_count": 34577, "top_destinations": [ { "destination": "DFW", "from_sfo_flight_count": 252, "from_oak_flight_count": 128 }, { "destination": "JFK", "from_sfo_flight_count": 148, "from_oak_flight_count": 0 }, { "destination": "LAX", "from_sfo_flight_count": 93, "from_oak_flight_count": 11 }, { "destination": "ORD", "from_sfo_flight_count": 80, "from_oak_flight_count": 0 }, { "destination": "BOS", "from_sfo_flight_count": 49, "from_oak_flight_count": 0 }, { "destination": "MIA", "from_sfo_flight_count": 32, "from_oak_flight_count": 0 }, { "destination": "STL", "from_sfo_flight_count": 17, "from_oak_flight_count": 0 }, { "destination": "HNL", "from_sfo_flight_count": 11, "from_oak_flight_count": 0 }, { "destination": "SNA", "from_sfo_flight_count": 6, "from_oak_flight_count": 0 }, { "destination": "ATL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 8, "flight_count": 2727 }, { "hour_of_day": 9, "flight_count": 2332 }, { "hour_of_day": 6, "flight_count": 2319 }, { "hour_of_day": 17, "flight_count": 2286 }, { "hour_of_day": 7, "flight_count": 2271 }, { "hour_of_day": 12, "flight_count": 2232 }, { "hour_of_day": 14, "flight_count": 2173 }, { "hour_of_day": 15, "flight_count": 2167 }, { "hour_of_day": 10, "flight_count": 2165 }, { "hour_of_day": 11, "flight_count": 2149 }, { "hour_of_day": 13, "flight_count": 2136 }, { "hour_of_day": 18, "flight_count": 2019 }, { "hour_of_day": 16, "flight_count": 1992 }, { "hour_of_day": 19, "flight_count": 1906 }, { "hour_of_day": 20, "flight_count": 1456 }, { "hour_of_day": 22, "flight_count": 682 }, { "hour_of_day": 21, "flight_count": 658 }, { "hour_of_day": 5, "flight_count": 432 }, { "hour_of_day": 23, "flight_count": 277 }, { "hour_of_day": 0, "flight_count": 158 }, { "hour_of_day": 1, "flight_count": 22 }, { "hour_of_day": 2, "flight_count": 13 }, { "hour_of_day": 3, "flight_count": 5 } ] }, { "nickname": "Northwest", "flight_count": 33580, "top_destinations": [ { "destination": "MSP", "from_sfo_flight_count": 281, "from_oak_flight_count": 0 }, { "destination": "DTW", "from_sfo_flight_count": 134, "from_oak_flight_count": 0 }, { "destination": "MEM", "from_sfo_flight_count": 81, "from_oak_flight_count": 0 }, { "destination": "HNL", "from_sfo_flight_count": 26, "from_oak_flight_count": 0 }, { "destination": "ABQ", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ALB", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ANC", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ATL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BIL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BWI", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 9, "flight_count": 3048 }, { "hour_of_day": 13, "flight_count": 2553 }, { "hour_of_day": 12, "flight_count": 2470 }, { "hour_of_day": 17, "flight_count": 2424 }, { "hour_of_day": 6, "flight_count": 2306 }, { "hour_of_day": 8, "flight_count": 2267 }, { "hour_of_day": 7, "flight_count": 2046 }, { "hour_of_day": 15, "flight_count": 1873 }, { "hour_of_day": 19, "flight_count": 1846 }, { "hour_of_day": 18, "flight_count": 1771 }, { "hour_of_day": 14, "flight_count": 1713 }, { "hour_of_day": 11, "flight_count": 1708 }, { "hour_of_day": 21, "flight_count": 1589 }, { "hour_of_day": 16, "flight_count": 1501 }, { "hour_of_day": 10, "flight_count": 1358 }, { "hour_of_day": 20, "flight_count": 872 }, { "hour_of_day": 22, "flight_count": 816 }, { "hour_of_day": 0, "flight_count": 785 }, { "hour_of_day": 5, "flight_count": 318 }, { "hour_of_day": 23, "flight_count": 159 }, { "hour_of_day": 1, "flight_count": 136 }, { "hour_of_day": 2, "flight_count": 18 }, { "hour_of_day": 4, "flight_count": 2 }, { "hour_of_day": 3, "flight_count": 1 } ] }, { "nickname": "United", "flight_count": 32757, "top_destinations": [ { "destination": "LAS", "from_sfo_flight_count": 299, "from_oak_flight_count": 0 }, { "destination": "ORD", "from_sfo_flight_count": 282, "from_oak_flight_count": 104 }, { "destination": "IAD", "from_sfo_flight_count": 235, "from_oak_flight_count": 54 }, { "destination": "DEN", "from_sfo_flight_count": 194, "from_oak_flight_count": 55 }, { "destination": "LAX", "from_sfo_flight_count": 182, "from_oak_flight_count": 36 }, { "destination": "SNA", "from_sfo_flight_count": 150, "from_oak_flight_count": 0 }, { "destination": "PHX", "from_sfo_flight_count": 140, "from_oak_flight_count": 0 }, { "destination": "PHL", "from_sfo_flight_count": 123, "from_oak_flight_count": 0 }, { "destination": "SAN", "from_sfo_flight_count": 118, "from_oak_flight_count": 0 }, { "destination": "EWR", "from_sfo_flight_count": 105, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 8, "flight_count": 3001 }, { "hour_of_day": 17, "flight_count": 2552 }, { "hour_of_day": 6, "flight_count": 2358 }, { "hour_of_day": 11, "flight_count": 2186 }, { "hour_of_day": 18, "flight_count": 2172 }, { "hour_of_day": 10, "flight_count": 2076 }, { "hour_of_day": 7, "flight_count": 1948 }, { "hour_of_day": 12, "flight_count": 1897 }, { "hour_of_day": 9, "flight_count": 1882 }, { "hour_of_day": 14, "flight_count": 1855 }, { "hour_of_day": 13, "flight_count": 1833 }, { "hour_of_day": 16, "flight_count": 1730 }, { "hour_of_day": 15, "flight_count": 1656 }, { "hour_of_day": 19, "flight_count": 1508 }, { "hour_of_day": 20, "flight_count": 1227 }, { "hour_of_day": 21, "flight_count": 1010 }, { "hour_of_day": 22, "flight_count": 752 }, { "hour_of_day": 5, "flight_count": 560 }, { "hour_of_day": 23, "flight_count": 434 }, { "hour_of_day": 0, "flight_count": 110 }, { "hour_of_day": 1, "flight_count": 10 } ] }, { "nickname": "Delta", "flight_count": 32130, "top_destinations": [ { "destination": "ATL", "from_sfo_flight_count": 97, "from_oak_flight_count": 10 }, { "destination": "CVG", "from_sfo_flight_count": 17, "from_oak_flight_count": 0 }, { "destination": "JFK", "from_sfo_flight_count": 7, "from_oak_flight_count": 0 }, { "destination": "HNL", "from_sfo_flight_count": 5, "from_oak_flight_count": 0 }, { "destination": "LAX", "from_sfo_flight_count": 3, "from_oak_flight_count": 0 }, { "destination": "SLC", "from_sfo_flight_count": 3, "from_oak_flight_count": 0 }, { "destination": "DFW", "from_sfo_flight_count": 2, "from_oak_flight_count": 0 }, { "destination": "ABE", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ANC", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BNA", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 8, "flight_count": 2554 }, { "hour_of_day": 10, "flight_count": 2293 }, { "hour_of_day": 12, "flight_count": 2184 }, { "hour_of_day": 16, "flight_count": 2163 }, { "hour_of_day": 17, "flight_count": 2060 }, { "hour_of_day": 14, "flight_count": 2022 }, { "hour_of_day": 19, "flight_count": 1926 }, { "hour_of_day": 18, "flight_count": 1918 }, { "hour_of_day": 13, "flight_count": 1870 }, { "hour_of_day": 11, "flight_count": 1839 }, { "hour_of_day": 6, "flight_count": 1660 }, { "hour_of_day": 20, "flight_count": 1655 }, { "hour_of_day": 7, "flight_count": 1586 }, { "hour_of_day": 15, "flight_count": 1570 }, { "hour_of_day": 9, "flight_count": 1376 }, { "hour_of_day": 0, "flight_count": 1144 }, { "hour_of_day": 21, "flight_count": 897 }, { "hour_of_day": 22, "flight_count": 633 }, { "hour_of_day": 5, "flight_count": 409 }, { "hour_of_day": 23, "flight_count": 311 }, { "hour_of_day": 1, "flight_count": 43 }, { "hour_of_day": 2, "flight_count": 7 }, { "hour_of_day": 4, "flight_count": 6 }, { "hour_of_day": 3, "flight_count": 4 } ] }, { "nickname": "Continental Express", "flight_count": 16074, "top_destinations": [ { "destination": "ABE", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ABI", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ALB", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BGR", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BPT", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BRO", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "CRW", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "DCA", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "DFW", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "DRO", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 15, "flight_count": 1418 }, { "hour_of_day": 13, "flight_count": 1348 }, { "hour_of_day": 9, "flight_count": 1330 }, { "hour_of_day": 6, "flight_count": 1171 }, { "hour_of_day": 11, "flight_count": 1171 }, { "hour_of_day": 17, "flight_count": 1171 }, { "hour_of_day": 18, "flight_count": 1016 }, { "hour_of_day": 12, "flight_count": 1014 }, { "hour_of_day": 14, "flight_count": 982 }, { "hour_of_day": 16, "flight_count": 938 }, { "hour_of_day": 7, "flight_count": 933 }, { "hour_of_day": 10, "flight_count": 851 }, { "hour_of_day": 20, "flight_count": 772 }, { "hour_of_day": 19, "flight_count": 744 }, { "hour_of_day": 8, "flight_count": 675 }, { "hour_of_day": 21, "flight_count": 266 }, { "hour_of_day": 5, "flight_count": 189 }, { "hour_of_day": 22, "flight_count": 63 }, { "hour_of_day": 23, "flight_count": 13 }, { "hour_of_day": 0, "flight_count": 5 }, { "hour_of_day": 1, "flight_count": 4 } ] }, { "nickname": "American Eagle", "flight_count": 15869, "top_destinations": [ { "destination": "BGR", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BNA", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "DSM", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "GGG", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "HPN", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "LAW", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "LBB", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "LIT", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "LRD", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "MEM", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 11, "flight_count": 1242 }, { "hour_of_day": 14, "flight_count": 1209 }, { "hour_of_day": 12, "flight_count": 1149 }, { "hour_of_day": 16, "flight_count": 1137 }, { "hour_of_day": 9, "flight_count": 1137 }, { "hour_of_day": 13, "flight_count": 1107 }, { "hour_of_day": 15, "flight_count": 1106 }, { "hour_of_day": 17, "flight_count": 1071 }, { "hour_of_day": 10, "flight_count": 1056 }, { "hour_of_day": 8, "flight_count": 1029 }, { "hour_of_day": 18, "flight_count": 882 }, { "hour_of_day": 6, "flight_count": 851 }, { "hour_of_day": 19, "flight_count": 775 }, { "hour_of_day": 7, "flight_count": 740 }, { "hour_of_day": 20, "flight_count": 601 }, { "hour_of_day": 21, "flight_count": 338 }, { "hour_of_day": 5, "flight_count": 279 }, { "hour_of_day": 22, "flight_count": 115 }, { "hour_of_day": 23, "flight_count": 42 }, { "hour_of_day": 1, "flight_count": 2 }, { "hour_of_day": 0, "flight_count": 1 } ] }, { "nickname": "Atlantic Southeast", "flight_count": 15769, "top_destinations": [ { "destination": "ABY", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "AGS", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ATL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "AVL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "CAE", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "CHA", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "DFW", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "LIT", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "MCN", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "MGM", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 10, "flight_count": 1083 }, { "hour_of_day": 13, "flight_count": 1035 }, { "hour_of_day": 18, "flight_count": 1027 }, { "hour_of_day": 16, "flight_count": 1023 }, { "hour_of_day": 12, "flight_count": 949 }, { "hour_of_day": 11, "flight_count": 900 }, { "hour_of_day": 19, "flight_count": 883 }, { "hour_of_day": 14, "flight_count": 868 }, { "hour_of_day": 9, "flight_count": 856 }, { "hour_of_day": 8, "flight_count": 844 }, { "hour_of_day": 15, "flight_count": 832 }, { "hour_of_day": 17, "flight_count": 818 }, { "hour_of_day": 7, "flight_count": 814 }, { "hour_of_day": 20, "flight_count": 704 }, { "hour_of_day": 22, "flight_count": 703 }, { "hour_of_day": 6, "flight_count": 665 }, { "hour_of_day": 0, "flight_count": 600 }, { "hour_of_day": 21, "flight_count": 515 }, { "hour_of_day": 5, "flight_count": 389 }, { "hour_of_day": 23, "flight_count": 230 }, { "hour_of_day": 4, "flight_count": 29 }, { "hour_of_day": 1, "flight_count": 2 } ] }, { "nickname": "America West", "flight_count": 9750, "top_destinations": [ { "destination": "LAS", "from_sfo_flight_count": 132, "from_oak_flight_count": 40 }, { "destination": "PHX", "from_sfo_flight_count": 78, "from_oak_flight_count": 124 }, { "destination": "ATL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BOI", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "DFW", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "DTW", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ELP", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "LAX", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "MKE", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "MSY", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 9, "flight_count": 699 }, { "hour_of_day": 8, "flight_count": 684 }, { "hour_of_day": 17, "flight_count": 684 }, { "hour_of_day": 14, "flight_count": 659 }, { "hour_of_day": 18, "flight_count": 636 }, { "hour_of_day": 11, "flight_count": 624 }, { "hour_of_day": 16, "flight_count": 567 }, { "hour_of_day": 7, "flight_count": 555 }, { "hour_of_day": 15, "flight_count": 508 }, { "hour_of_day": 19, "flight_count": 504 }, { "hour_of_day": 20, "flight_count": 485 }, { "hour_of_day": 12, "flight_count": 478 }, { "hour_of_day": 13, "flight_count": 463 }, { "hour_of_day": 10, "flight_count": 461 }, { "hour_of_day": 6, "flight_count": 439 }, { "hour_of_day": 21, "flight_count": 423 }, { "hour_of_day": 0, "flight_count": 328 }, { "hour_of_day": 23, "flight_count": 207 }, { "hour_of_day": 22, "flight_count": 176 }, { "hour_of_day": 5, "flight_count": 122 }, { "hour_of_day": 1, "flight_count": 40 }, { "hour_of_day": 2, "flight_count": 5 }, { "hour_of_day": 3, "flight_count": 3 } ] }, { "nickname": "Alaska", "flight_count": 8453, "top_destinations": [ { "destination": "SEA", "from_sfo_flight_count": 95, "from_oak_flight_count": 201 }, { "destination": "PDX", "from_sfo_flight_count": 66, "from_oak_flight_count": 44 }, { "destination": "LAX", "from_sfo_flight_count": 40, "from_oak_flight_count": 0 }, { "destination": "PSP", "from_sfo_flight_count": 20, "from_oak_flight_count": 0 }, { "destination": "BUR", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "FAI", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "LAS", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "SFO", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "SJC", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "ANC", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 6, "flight_count": 836 }, { "hour_of_day": 7, "flight_count": 603 }, { "hour_of_day": 10, "flight_count": 591 }, { "hour_of_day": 20, "flight_count": 575 }, { "hour_of_day": 13, "flight_count": 574 }, { "hour_of_day": 19, "flight_count": 565 }, { "hour_of_day": 17, "flight_count": 555 }, { "hour_of_day": 9, "flight_count": 507 }, { "hour_of_day": 15, "flight_count": 490 }, { "hour_of_day": 18, "flight_count": 465 }, { "hour_of_day": 16, "flight_count": 450 }, { "hour_of_day": 12, "flight_count": 393 }, { "hour_of_day": 21, "flight_count": 380 }, { "hour_of_day": 11, "flight_count": 344 }, { "hour_of_day": 14, "flight_count": 343 }, { "hour_of_day": 8, "flight_count": 333 }, { "hour_of_day": 22, "flight_count": 160 }, { "hour_of_day": 23, "flight_count": 132 }, { "hour_of_day": 1, "flight_count": 56 }, { "hour_of_day": 0, "flight_count": 48 }, { "hour_of_day": 5, "flight_count": 26 }, { "hour_of_day": 2, "flight_count": 23 }, { "hour_of_day": 3, "flight_count": 4 } ] }, { "nickname": "Continental", "flight_count": 7139, "top_destinations": [ { "destination": "IAH", "from_sfo_flight_count": 19, "from_oak_flight_count": 8 }, { "destination": "CLE", "from_sfo_flight_count": 2, "from_oak_flight_count": 0 }, { "destination": "AUS", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BDL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BOS", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BRO", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BTR", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BWI", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "DTW", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "GPT", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 9, "flight_count": 619 }, { "hour_of_day": 15, "flight_count": 608 }, { "hour_of_day": 7, "flight_count": 587 }, { "hour_of_day": 17, "flight_count": 552 }, { "hour_of_day": 13, "flight_count": 536 }, { "hour_of_day": 11, "flight_count": 527 }, { "hour_of_day": 6, "flight_count": 462 }, { "hour_of_day": 14, "flight_count": 430 }, { "hour_of_day": 12, "flight_count": 428 }, { "hour_of_day": 18, "flight_count": 414 }, { "hour_of_day": 10, "flight_count": 394 }, { "hour_of_day": 16, "flight_count": 387 }, { "hour_of_day": 20, "flight_count": 330 }, { "hour_of_day": 19, "flight_count": 329 }, { "hour_of_day": 8, "flight_count": 302 }, { "hour_of_day": 21, "flight_count": 134 }, { "hour_of_day": 5, "flight_count": 64 }, { "hour_of_day": 22, "flight_count": 26 }, { "hour_of_day": 23, "flight_count": 5 }, { "hour_of_day": 0, "flight_count": 3 }, { "hour_of_day": 3, "flight_count": 1 }, { "hour_of_day": 1, "flight_count": 1 } ] }, { "nickname": "Jetblue", "flight_count": 4842, "top_destinations": [ { "destination": "DEN", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "LGA", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "MCO", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "MSY", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "PHX", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "RSW", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "SAN", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "SLC", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "SMF", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "TPA", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 6, "flight_count": 396 }, { "hour_of_day": 11, "flight_count": 376 }, { "hour_of_day": 17, "flight_count": 315 }, { "hour_of_day": 7, "flight_count": 314 }, { "hour_of_day": 19, "flight_count": 314 }, { "hour_of_day": 10, "flight_count": 308 }, { "hour_of_day": 15, "flight_count": 275 }, { "hour_of_day": 8, "flight_count": 272 }, { "hour_of_day": 20, "flight_count": 266 }, { "hour_of_day": 13, "flight_count": 262 }, { "hour_of_day": 9, "flight_count": 261 }, { "hour_of_day": 16, "flight_count": 238 }, { "hour_of_day": 21, "flight_count": 231 }, { "hour_of_day": 14, "flight_count": 204 }, { "hour_of_day": 18, "flight_count": 194 }, { "hour_of_day": 12, "flight_count": 177 }, { "hour_of_day": 22, "flight_count": 175 }, { "hour_of_day": 23, "flight_count": 100 }, { "hour_of_day": 0, "flight_count": 82 }, { "hour_of_day": 5, "flight_count": 32 }, { "hour_of_day": 1, "flight_count": 28 }, { "hour_of_day": 2, "flight_count": 14 }, { "hour_of_day": 3, "flight_count": 6 }, { "hour_of_day": 4, "flight_count": 2 } ] }, { "nickname": "Comair", "flight_count": 4420, "top_destinations": [ { "destination": "ATW", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "AVL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BHM", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "BTV", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "CVG", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "EWR", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "FNT", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "GRB", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "GSO", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "HTS", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 9, "flight_count": 357 }, { "hour_of_day": 15, "flight_count": 292 }, { "hour_of_day": 10, "flight_count": 276 }, { "hour_of_day": 12, "flight_count": 268 }, { "hour_of_day": 11, "flight_count": 258 }, { "hour_of_day": 19, "flight_count": 255 }, { "hour_of_day": 17, "flight_count": 249 }, { "hour_of_day": 16, "flight_count": 248 }, { "hour_of_day": 6, "flight_count": 248 }, { "hour_of_day": 13, "flight_count": 242 }, { "hour_of_day": 20, "flight_count": 238 }, { "hour_of_day": 18, "flight_count": 219 }, { "hour_of_day": 14, "flight_count": 215 }, { "hour_of_day": 8, "flight_count": 201 }, { "hour_of_day": 21, "flight_count": 191 }, { "hour_of_day": 7, "flight_count": 183 }, { "hour_of_day": 5, "flight_count": 161 }, { "hour_of_day": 23, "flight_count": 121 }, { "hour_of_day": 0, "flight_count": 113 }, { "hour_of_day": 22, "flight_count": 79 }, { "hour_of_day": 1, "flight_count": 5 }, { "hour_of_day": 4, "flight_count": 1 } ] }, { "nickname": "ATA", "flight_count": 3033, "top_destinations": [ { "destination": "MDW", "from_sfo_flight_count": 30, "from_oak_flight_count": 0 }, { "destination": "IND", "from_sfo_flight_count": 4, "from_oak_flight_count": 0 }, { "destination": "EWR", "from_sfo_flight_count": 3, "from_oak_flight_count": 0 }, { "destination": "DFW", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "LGA", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "MIA", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "SFO", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "CLT", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "FLL", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 }, { "destination": "LAS", "from_sfo_flight_count": 0, "from_oak_flight_count": 0 } ], "by_hour_of_day": [ { "hour_of_day": 6, "flight_count": 301 }, { "hour_of_day": 18, "flight_count": 214 }, { "hour_of_day": 12, "flight_count": 212 }, { "hour_of_day": 10, "flight_count": 212 }, { "hour_of_day": 19, "flight_count": 201 }, { "hour_of_day": 14, "flight_count": 197 }, { "hour_of_day": 8, "flight_count": 183 }, { "hour_of_day": 13, "flight_count": 176 }, { "hour_of_day": 9, "flight_count": 164 }, { "hour_of_day": 15, "flight_count": 164 }, { "hour_of_day": 7, "flight_count": 159 }, { "hour_of_day": 17, "flight_count": 144 }, { "hour_of_day": 11, "flight_count": 124 }, { "hour_of_day": 16, "flight_count": 124 }, { "hour_of_day": 20, "flight_count": 114 }, { "hour_of_day": 21, "flight_count": 114 }, { "hour_of_day": 5, "flight_count": 79 }, { "hour_of_day": 22, "flight_count": 61 }, { "hour_of_day": 0, "flight_count": 45 }, { "hour_of_day": 23, "flight_count": 41 }, { "hour_of_day": 1, "flight_count": 3 }, { "hour_of_day": 3, "flight_count": 1 } ] } ]
WITH __stage0 AS ( SELECT group_set, carriers_0."nickname" as "nickname__0", CASE WHEN group_set=0 THEN COUNT(1) END as "flight_count__0", CASE WHEN group_set=1 THEN base."destination" END as "destination__1", (CASE WHEN group_set=1 THEN COUNT(CASE WHEN base."origin"='SFO' THEN 1 END) END) as "from_sfo_flight_count__1", (CASE WHEN group_set=1 THEN COUNT(CASE WHEN base."origin"='OAK' THEN 1 END) END) as "from_oak_flight_count__1", CASE WHEN group_set=2 THEN EXTRACT(hour FROM base."dep_time") END as "hour_of_day__2", CASE WHEN group_set=2 THEN COUNT(1) END as "flight_count__2" 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,2,1)) as group_set ) as group_set GROUP BY 1,2,4,7 ) SELECT "nickname__0" as "nickname", MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count", COALESCE(LIST({ "destination": "destination__1", "from_sfo_flight_count": "from_sfo_flight_count__1", "from_oak_flight_count": "from_oak_flight_count__1"} ORDER BY "from_sfo_flight_count__1" desc NULLS LAST) FILTER (WHERE group_set=1)[1:10],[]) as "top_destinations", COALESCE(LIST({ "hour_of_day": "hour_of_day__2", "flight_count": "flight_count__2"} ORDER BY "flight_count__2" desc NULLS LAST) FILTER (WHERE group_set=2),[]) as "by_hour_of_day" FROM __stage0 GROUP BY 1 ORDER BY 2 desc NULLS LAST