What Is Malloy
Lists
There are two types of bar charts. Two measure bar charts (gradient bar charts) and Two Dimension Bar Charts (stacked bar charts).
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() aircraft_count is count(tail_num) }
Simple Bar Chart
A basic bar chart takes a table where the first column is a string and the second is a number.
# 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 flights LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON flights."carrier"=carriers_0."code" GROUP BY 1 ORDER BY 2 desc NULLS LAST
Two Measures
This chart looks at flights and counts the number of aircraft owned by each carrier. It also, using a gradient, shows the number of flights made per plane.
# bar_chart run: flights -> { group_by: carriers.nickname aggregate: aircraft_count aggregate: flights_per_aircraft is flight_count / aircraft_count }
[ { "nickname": "Southwest", "aircraft_count": 8, "flights_per_aircraft": 11093.875 }, { "nickname": "USAir", "aircraft_count": 6, "flights_per_aircraft": 6280.5 }, { "nickname": "American", "aircraft_count": 6, "flights_per_aircraft": 5762.833333333333 }, { "nickname": "Northwest", "aircraft_count": 6, "flights_per_aircraft": 5596.666666666667 }, { "nickname": "United", "aircraft_count": 6, "flights_per_aircraft": 5459.5 }, { "nickname": "Delta", "aircraft_count": 5, "flights_per_aircraft": 6426 }, { "nickname": "Continental Express", "aircraft_count": 4, "flights_per_aircraft": 4018.5 }, { "nickname": "American Eagle", "aircraft_count": 4, "flights_per_aircraft": 3967.25 }, { "nickname": "Atlantic Southeast", "aircraft_count": 3, "flights_per_aircraft": 5256.333333333333 }, { "nickname": "Jetblue", "aircraft_count": 2, "flights_per_aircraft": 2421 }, { "nickname": "America West", "aircraft_count": 1, "flights_per_aircraft": 9750 }, { "nickname": "Alaska", "aircraft_count": 1, "flights_per_aircraft": 8453 }, { "nickname": "ATA", "aircraft_count": 1, "flights_per_aircraft": 3033 }, { "nickname": "Comair", "aircraft_count": 1, "flights_per_aircraft": 4420 }, { "nickname": "Continental", "aircraft_count": 1, "flights_per_aircraft": 7139 } ]
SELECT carriers_0."nickname" as "nickname", count(distinct flights."tail_num") as "aircraft_count", (COUNT( 1))*1.0/(count(distinct flights."tail_num")) as "flights_per_aircraft" FROM '../data/flights.parquet' as flights LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON flights."carrier"=carriers_0."code" GROUP BY 1 ORDER BY 2 desc NULLS LAST
Two Dimensions
In this case we are going to look at carriers by flight count and stack the destination. We are only going to look at flights with the destination SFO, OAK or SJC.
# bar_chart run: flights -> { where: destination ? 'SFO' | 'OAK' | 'SJC' group_by: carriers.nickname aggregate: flight_count group_by: destination }
[ { "nickname": "Southwest", "flight_count": 3967, "destination": "OAK" }, { "nickname": "United", "flight_count": 2432, "destination": "SFO" }, { "nickname": "Southwest", "flight_count": 2143, "destination": "SJC" }, { "nickname": "American", "flight_count": 686, "destination": "SFO" }, { "nickname": "American", "flight_count": 558, "destination": "SJC" }, { "nickname": "Northwest", "flight_count": 520, "destination": "SFO" }, { "nickname": "Alaska", "flight_count": 367, "destination": "SJC" }, { "nickname": "United", "flight_count": 318, "destination": "SJC" }, { "nickname": "Jetblue", "flight_count": 297, "destination": "OAK" }, { "nickname": "United", "flight_count": 249, "destination": "OAK" }, { "nickname": "Alaska", "flight_count": 248, "destination": "OAK" }, { "nickname": "Alaska", "flight_count": 223, "destination": "SFO" }, { "nickname": "America West", "flight_count": 209, "destination": "SFO" }, { "nickname": "America West", "flight_count": 180, "destination": "SJC" }, { "nickname": "Northwest", "flight_count": 168, "destination": "SJC" }, { "nickname": "America West", "flight_count": 162, "destination": "OAK" }, { "nickname": "USAir", "flight_count": 156, "destination": "SFO" }, { "nickname": "American", "flight_count": 141, "destination": "OAK" }, { "nickname": "Delta", "flight_count": 132, "destination": "SFO" }, { "nickname": "Southwest", "flight_count": 86, "destination": "SFO" }, { "nickname": "ATA", "flight_count": 37, "destination": "SFO" }, { "nickname": "Continental", "flight_count": 35, "destination": "SJC" }, { "nickname": "Jetblue", "flight_count": 21, "destination": "SJC" }, { "nickname": "Continental", "flight_count": 21, "destination": "SFO" }, { "nickname": "Delta", "flight_count": 19, "destination": "SJC" }, { "nickname": "Delta", "flight_count": 10, "destination": "OAK" }, { "nickname": "ATA", "flight_count": 9, "destination": "SJC" }, { "nickname": "Continental", "flight_count": 8, "destination": "OAK" } ]
SELECT carriers_0."nickname" as "nickname", COUNT( 1) as "flight_count", flights."destination" as "destination" FROM '../data/flights.parquet' as flights LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON flights."carrier"=carriers_0."code" WHERE (flights."destination"='SFO')or((flights."destination"='OAK')or(flights."destination"='SJC')) GROUP BY 1,3 ORDER BY 2 desc NULLS LAST
We could flip the dimensions around and look at the airports' flights by carrier.
# bar_chart run: flights -> { where: destination ? 'SFO' | 'OAK' | 'SJC' group_by: destination aggregate: flight_count group_by: carriers.nickname }
[ { "destination": "OAK", "flight_count": 3967, "nickname": "Southwest" }, { "destination": "SFO", "flight_count": 2432, "nickname": "United" }, { "destination": "SJC", "flight_count": 2143, "nickname": "Southwest" }, { "destination": "SFO", "flight_count": 686, "nickname": "American" }, { "destination": "SJC", "flight_count": 558, "nickname": "American" }, { "destination": "SFO", "flight_count": 520, "nickname": "Northwest" }, { "destination": "SJC", "flight_count": 367, "nickname": "Alaska" }, { "destination": "SJC", "flight_count": 318, "nickname": "United" }, { "destination": "OAK", "flight_count": 297, "nickname": "Jetblue" }, { "destination": "OAK", "flight_count": 249, "nickname": "United" }, { "destination": "OAK", "flight_count": 248, "nickname": "Alaska" }, { "destination": "SFO", "flight_count": 223, "nickname": "Alaska" }, { "destination": "SFO", "flight_count": 209, "nickname": "America West" }, { "destination": "SJC", "flight_count": 180, "nickname": "America West" }, { "destination": "SJC", "flight_count": 168, "nickname": "Northwest" }, { "destination": "OAK", "flight_count": 162, "nickname": "America West" }, { "destination": "SFO", "flight_count": 156, "nickname": "USAir" }, { "destination": "OAK", "flight_count": 141, "nickname": "American" }, { "destination": "SFO", "flight_count": 132, "nickname": "Delta" }, { "destination": "SFO", "flight_count": 86, "nickname": "Southwest" }, { "destination": "SFO", "flight_count": 37, "nickname": "ATA" }, { "destination": "SJC", "flight_count": 35, "nickname": "Continental" }, { "destination": "SFO", "flight_count": 21, "nickname": "Continental" }, { "destination": "SJC", "flight_count": 21, "nickname": "Jetblue" }, { "destination": "SJC", "flight_count": 19, "nickname": "Delta" }, { "destination": "OAK", "flight_count": 10, "nickname": "Delta" }, { "destination": "SJC", "flight_count": 9, "nickname": "ATA" }, { "destination": "OAK", "flight_count": 8, "nickname": "Continental" } ]
SELECT flights."destination" as "destination", COUNT( 1) as "flight_count", carriers_0."nickname" as "nickname" FROM '../data/flights.parquet' as flights LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON flights."carrier"=carriers_0."code" WHERE (flights."destination"='SFO')or((flights."destination"='OAK')or(flights."destination"='SJC')) GROUP BY 1,3 ORDER BY 2 desc NULLS LAST
Nested bar Charts
Barcharts can be used in nested queries
run: flights -> { group_by: carriers.nickname aggregate: flight_count # bar_chart nest: top_destinations is { group_by: destination aggregate: 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 | flight_​count | top_​destinations | by_​hour_​of_​day |
|---|---|---|---|
| Southwest | 88,751 | ||
| USAir | 37,683 | ||
| American | 34,577 | ||
| Northwest | 33,580 | ||
| United | 32,757 | ||
| Delta | 32,130 | ||
| Continental Express | 16,074 | ||
| American Eagle | 15,869 | ||
| Atlantic Southeast | 15,769 | ||
| America West | 9,750 | ||
| Alaska | 8,453 | ||
| Continental | 7,139 | ||
| Jetblue | 4,842 | ||
| Comair | 4,420 | ||
| ATA | 3,033 |
[ { "nickname": "Southwest", "flight_count": 88751, "top_destinations": [ { "destination": "PHX", "flight_count": 6437 }, { "destination": "LAS", "flight_count": 6288 }, { "destination": "BWI", "flight_count": 5481 }, { "destination": "MDW", "flight_count": 4699 }, { "destination": "LAX", "flight_count": 4279 }, { "destination": "OAK", "flight_count": 3967 }, { "destination": "HOU", "flight_count": 3217 }, { "destination": "BNA", "flight_count": 3165 }, { "destination": "SAN", "flight_count": 2874 }, { "destination": "MCI", "flight_count": 2588 } ], "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", "flight_count": 6433 }, { "destination": "PHL", "flight_count": 5762 }, { "destination": "PIT", "flight_count": 4104 }, { "destination": "DCA", "flight_count": 2911 }, { "destination": "LGA", "flight_count": 1417 }, { "destination": "BOS", "flight_count": 1217 }, { "destination": "DFW", "flight_count": 657 }, { "destination": "RDU", "flight_count": 639 }, { "destination": "IAH", "flight_count": 604 }, { "destination": "ATL", "flight_count": 523 } ], "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", "flight_count": 8745 }, { "destination": "ORD", "flight_count": 5147 }, { "destination": "LAX", "flight_count": 1948 }, { "destination": "LGA", "flight_count": 1056 }, { "destination": "MIA", "flight_count": 1051 }, { "destination": "JFK", "flight_count": 949 }, { "destination": "STL", "flight_count": 714 }, { "destination": "SFO", "flight_count": 686 }, { "destination": "AUS", "flight_count": 671 }, { "destination": "BOS", "flight_count": 606 } ], "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", "flight_count": 8667 }, { "destination": "DTW", "flight_count": 6587 }, { "destination": "MEM", "flight_count": 1990 }, { "destination": "LAX", "flight_count": 990 }, { "destination": "MCO", "flight_count": 847 }, { "destination": "SEA", "flight_count": 755 }, { "destination": "LGA", "flight_count": 686 }, { "destination": "BOS", "flight_count": 671 }, { "destination": "LAS", "flight_count": 635 }, { "destination": "DCA", "flight_count": 602 } ], "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": "ORD", "flight_count": 6807 }, { "destination": "DEN", "flight_count": 4985 }, { "destination": "IAD", "flight_count": 2589 }, { "destination": "SFO", "flight_count": 2432 }, { "destination": "LAX", "flight_count": 2318 }, { "destination": "LAS", "flight_count": 1339 }, { "destination": "MCO", "flight_count": 856 }, { "destination": "SEA", "flight_count": 730 }, { "destination": "PHX", "flight_count": 662 }, { "destination": "SAN", "flight_count": 644 } ], "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", "flight_count": 8404 }, { "destination": "LGA", "flight_count": 3294 }, { "destination": "DCA", "flight_count": 1966 }, { "destination": "BOS", "flight_count": 1732 }, { "destination": "MCO", "flight_count": 1642 }, { "destination": "CVG", "flight_count": 1490 }, { "destination": "SLC", "flight_count": 892 }, { "destination": "FLL", "flight_count": 765 }, { "destination": "TPA", "flight_count": 695 }, { "destination": "DFW", "flight_count": 672 } ], "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": "CLE", "flight_count": 3001 }, { "destination": "IAH", "flight_count": 2862 }, { "destination": "EWR", "flight_count": 2165 }, { "destination": "CLT", "flight_count": 253 }, { "destination": "DAY", "flight_count": 235 }, { "destination": "IND", "flight_count": 223 }, { "destination": "CVG", "flight_count": 204 }, { "destination": "HPN", "flight_count": 192 }, { "destination": "STL", "flight_count": 192 }, { "destination": "IAD", "flight_count": 189 } ], "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": 11, "flight_count": 1171 }, { "hour_of_day": 17, "flight_count": 1171 }, { "hour_of_day": 6, "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": "DFW", "flight_count": 6128 }, { "destination": "ORD", "flight_count": 976 }, { "destination": "ACT", "flight_count": 492 }, { "destination": "TYR", "flight_count": 482 }, { "destination": "SPS", "flight_count": 456 }, { "destination": "CLL", "flight_count": 445 }, { "destination": "LAW", "flight_count": 429 }, { "destination": "JFK", "flight_count": 418 }, { "destination": "BOS", "flight_count": 390 }, { "destination": "ILE", "flight_count": 372 } ], "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": "ATL", "flight_count": 7372 }, { "destination": "PFN", "flight_count": 787 }, { "destination": "AGS", "flight_count": 696 }, { "destination": "CHA", "flight_count": 682 }, { "destination": "AVL", "flight_count": 657 }, { "destination": "CSG", "flight_count": 651 }, { "destination": "TRI", "flight_count": 549 }, { "destination": "MGM", "flight_count": 504 }, { "destination": "GNV", "flight_count": 493 }, { "destination": "DFW", "flight_count": 398 } ], "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": "PHX", "flight_count": 3711 }, { "destination": "LAS", "flight_count": 1219 }, { "destination": "SNA", "flight_count": 422 }, { "destination": "SMF", "flight_count": 372 }, { "destination": "DEN", "flight_count": 302 }, { "destination": "LAX", "flight_count": 293 }, { "destination": "DFW", "flight_count": 274 }, { "destination": "ABQ", "flight_count": 214 }, { "destination": "SFO", "flight_count": 209 }, { "destination": "PDX", "flight_count": 205 } ], "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", "flight_count": 3008 }, { "destination": "PDX", "flight_count": 1043 }, { "destination": "LAX", "flight_count": 709 }, { "destination": "LAS", "flight_count": 417 }, { "destination": "ANC", "flight_count": 399 }, { "destination": "SAN", "flight_count": 386 }, { "destination": "SJC", "flight_count": 367 }, { "destination": "PHX", "flight_count": 348 }, { "destination": "GEG", "flight_count": 249 }, { "destination": "OAK", "flight_count": 248 } ], "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", "flight_count": 1962 }, { "destination": "EWR", "flight_count": 1064 }, { "destination": "CLE", "flight_count": 704 }, { "destination": "BOS", "flight_count": 270 }, { "destination": "ATL", "flight_count": 248 }, { "destination": "MSY", "flight_count": 178 }, { "destination": "ORD", "flight_count": 141 }, { "destination": "MIA", "flight_count": 139 }, { "destination": "MCO", "flight_count": 121 }, { "destination": "DTW", "flight_count": 121 } ], "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": "JFK", "flight_count": 1753 }, { "destination": "LGB", "flight_count": 472 }, { "destination": "FLL", "flight_count": 373 }, { "destination": "OAK", "flight_count": 297 }, { "destination": "MCO", "flight_count": 245 }, { "destination": "IAD", "flight_count": 203 }, { "destination": "BOS", "flight_count": 185 }, { "destination": "LAS", "flight_count": 146 }, { "destination": "PBI", "flight_count": 143 }, { "destination": "TPA", "flight_count": 141 } ], "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": "CVG", "flight_count": 1512 }, { "destination": "ATL", "flight_count": 228 }, { "destination": "LGA", "flight_count": 192 }, { "destination": "JFK", "flight_count": 139 }, { "destination": "MCO", "flight_count": 88 }, { "destination": "GSO", "flight_count": 67 }, { "destination": "DCA", "flight_count": 66 }, { "destination": "JAX", "flight_count": 63 }, { "destination": "RDU", "flight_count": 61 }, { "destination": "HSV", "flight_count": 58 } ], "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", "flight_count": 1313 }, { "destination": "LGA", "flight_count": 208 }, { "destination": "IND", "flight_count": 197 }, { "destination": "MSP", "flight_count": 162 }, { "destination": "DFW", "flight_count": 132 }, { "destination": "DEN", "flight_count": 122 }, { "destination": "EWR", "flight_count": 102 }, { "destination": "RSW", "flight_count": 73 }, { "destination": "DCA", "flight_count": 69 }, { "destination": "PHL", "flight_count": 66 } ], "by_hour_of_day": [ { "hour_of_day": 6, "flight_count": 301 }, { "hour_of_day": 18, "flight_count": 214 }, { "hour_of_day": 10, "flight_count": 212 }, { "hour_of_day": 12, "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 flights."destination" END as "destination__1", CASE WHEN group_set=1 THEN COUNT( 1) END as "flight_count__1", CASE WHEN group_set=2 THEN EXTRACT(hour FROM flights."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 flights LEFT JOIN '../data/carriers.parquet' AS carriers_0 ON flights."carrier"=carriers_0."code" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set ) as group_set GROUP BY 1,2,4,6 ) SELECT "nickname__0" as "nickname", MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count", COALESCE(LIST({ "destination": "destination__1", "flight_count": "flight_count__1"} ORDER BY "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