What Is Malloy
Malloy CLI
A semantic data model contains prebuilt calculations and relationships for a set of data. This article will show you the basics of querying a semantic data model in Malloy.
Let’s start simply, we'll define: dimension, measure, aggregating query, and lookup query.
Data for Kindergarteners
Given that we learn about data in Kindergarten, it is really surprising how hard it is to query data in the "big" world. In kindergarten data, we notice attributes about things, make piles of things, and count things in the piles. We do this even before we learn any "real" math. Strangely, noticing attributes about things and counting things is almost all there is to working with data.
Two types of queries, "Where's Waldo" and "Making Piles"
There are really two types of queries in the world, lookup and aggregating.
Lookup queries are pretty easy. Google search is a lookup query. To search, type in some terms then see a list of results. Searching in SQL this often looks like SELECT * FROM <something> WHERE <FILTER>.
The interesting queries, the kindergarten queries, are aggregating. Aggregating queries tell you something about a set of data. An aggregating query has two main parts, the dimensions and measures.
Dimensions are the attributes you use to decide which pile the thing goes in.
A measure is something you can say about the pile. "How many objects?", "How much does this pile weigh?", "What is the average size of an object in this pile?"
"OK class, let's take this pile of coins and separate them. How many coins are pennies? How many coins are nickels? Dimes? Quarters?"
Aggregating queries tell us things about datasets. Lookup queries find things.
In Malloy Syntax
Lookup (Projection) - use select: to return rows without aggregation:
run: duckdb.table('../data/flights.parquet') -> { select: origin, destination, carrier, dep_time where: carrier = 'WN' limit: 5 }
[ { "origin": "MCO", "destination": "ABQ", "carrier": "WN", "dep_time": "2002-11-08T07:35:00.000Z" }, { "origin": "MCO", "destination": "ABQ", "carrier": "WN", "dep_time": "2003-03-19T16:25:00.000Z" }, { "origin": "MCO", "destination": "ABQ", "carrier": "WN", "dep_time": "2005-10-20T15:35:00.000Z" }, { "origin": "MCO", "destination": "ABQ", "carrier": "WN", "dep_time": "2000-06-11T17:00:00.000Z" }, { "origin": "MCO", "destination": "ABQ", "carrier": "WN", "dep_time": "2005-09-29T15:35:00.000Z" } ]
SELECT base."origin" as "origin", base."destination" as "destination", base."carrier" as "carrier", base."dep_time" as "dep_time" FROM '../data/flights.parquet' as base WHERE base."carrier"='WN' LIMIT 5
Aggregating (Reduction) - use group_by: and aggregate: to group and measure:
run: duckdb.table('../data/flights.parquet') -> { group_by: carrier aggregate: flight_count is count() limit: 5 }
[ { "carrier": "WN", "flight_count": 88751 }, { "carrier": "US", "flight_count": 37683 }, { "carrier": "AA", "flight_count": 34577 }, { "carrier": "NW", "flight_count": 33580 }, { "carrier": "UA", "flight_count": 32757 } ]
SELECT base."carrier" as "carrier", COUNT(1) as "flight_count" FROM '../data/flights.parquet' as base GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 5
For SQL users:
select:is likeSELECT ... WHERE(no GROUP BY).group_by:+aggregate:is likeSELECT ... GROUP BY.
SQL World
In the SQL world, the interface to your data is just that, data. The data sits in tables. Everytime, you ask a question (run a query), you need to restate all the things about the data: the calculations, the relationships between tables. The unit of reusability is a table. You can run a query that makes a new table. You can turn a query into a SQL View, which is basically a table based on a calculation.
The Semantic Data Model is the interface to your data
In a Semantic Data Model, the calculations (dimensions and measures) are reusable. The join relationships are built into the semantic data model. The calculations (common ways of looking at data) are coded into the semantic data model. The act of querying becomes simply picking dimensions and measures, filtering the data, and sorting the results. This simplification is powerful in that the calculations are always vetted, so it becomes much harder to get incorrect results.
A Simple Example
Below is a semantic data model for some flight data. For now, let's not concern ourselves with how this model is built but instead focus on how we can ask questions of the model. In the semantic model, dimensions, measures, and joined relationships are all exposed as a list of variables.
Each flight has an origin, destination and carrier. Flights can be delayed. Each flight has an aircraft that made the flight, and more. The semantic data model provides us with the dimensions (in blue), the measures (in orange), and the graph of related objects. This model is encoded in a file called ‘flights.malloy’.
import {flights} from 'flights.malloy' 
Step 10: We'll jump to the end with a complex example
A semantic data model lets you do complicated things simply. Shamelessly, to ensure you read the full article, I'll start with a seemingly complex example and then explain how we got here. It's actually quite simple.
The dashboard below shows flights from airports in California, where you can go, and which carriers will take you there. It shows how this has changed over time.
# dashboard run: flights -> origin_name + metrics + { where: origin.state = 'CA' nest: destination_name + metrics + carrier_list + {limit: 6} # bar_chart nest: carrier_name + flight_count # line_chart nest: dep_month + flight_count }
[ { "origin_name": "LAX - LOS ANGELES", "flight_count": 11077, "total_distance": "12861847", "percent_of_flights": 0.2723629210720433, "destination_name": [ { "destination_name": "LAS - LAS VEGAS", "flight_count": 1073, "total_distance": "253228", "percent_of_flights": 0.09686738286539677, "cl": [ { "nickname": "Southwest", "flight_count": 556 }, { "nickname": "United", "flight_count": 255 }, { "nickname": "America West", "flight_count": 136 }, { "nickname": "American", "flight_count": 82 }, { "nickname": "Northwest", "flight_count": 35 }, { "nickname": "Delta", "flight_count": 9 } ] }, { "destination_name": "OAK - OAKLAND", "flight_count": 868, "total_distance": "292516", "percent_of_flights": 0.07836056694050736, "cl": [ { "nickname": "Southwest", "flight_count": 821 }, { "nickname": "United", "flight_count": 32 }, { "nickname": "American", "flight_count": 15 } ] }, { "destination_name": "PHX - PHOENIX", "flight_count": 737, "total_distance": "272690", "percent_of_flights": 0.06653426017874876, "cl": [ { "nickname": "Southwest", "flight_count": 552 }, { "nickname": "America West", "flight_count": 159 }, { "nickname": "United", "flight_count": 15 }, { "nickname": "American", "flight_count": 11 } ] }, { "destination_name": "IAD - WASHINGTON", "flight_count": 615, "total_distance": "1407120", "percent_of_flights": 0.05552044777466823, "cl": [ { "nickname": "United", "flight_count": 330 }, { "nickname": "American", "flight_count": 285 } ] }, { "destination_name": "SEA - SEATTLE", "flight_count": 528, "total_distance": "503712", "percent_of_flights": 0.047666335650446874, "cl": [ { "nickname": "Alaska", "flight_count": 418 }, { "nickname": "United", "flight_count": 110 } ] }, { "destination_name": "ORD - CHICAGO", "flight_count": 523, "total_distance": "912635", "percent_of_flights": 0.047214949896181274, "cl": [ { "nickname": "United", "flight_count": 382 }, { "nickname": "American", "flight_count": 141 } ] } ], "carrier_name": [ { "carrier_name": "Southwest", "flight_count": 4282 }, { "carrier_name": "United", "flight_count": 2319 }, { "carrier_name": "American", "flight_count": 1951 }, { "carrier_name": "Northwest", "flight_count": 993 }, { "carrier_name": "Alaska", "flight_count": 695 }, { "carrier_name": "Delta", "flight_count": 343 }, { "carrier_name": "America West", "flight_count": 295 }, { "carrier_name": "USAir", "flight_count": 135 }, { "carrier_name": "ATA", "flight_count": 32 }, { "carrier_name": "American Eagle", "flight_count": 25 }, { "carrier_name": "Continental", "flight_count": 6 }, { "carrier_name": "Continental Express", "flight_count": 1 } ], "dep_month": [ { "dep_month": "2005-12-01T00:00:00.000Z", "flight_count": 165 }, { "dep_month": "2005-11-01T00:00:00.000Z", "flight_count": 182 }, { "dep_month": "2005-10-01T00:00:00.000Z", "flight_count": 163 }, { "dep_month": "2005-09-01T00:00:00.000Z", "flight_count": 154 }, { "dep_month": "2005-08-01T00:00:00.000Z", "flight_count": 171 }, { "dep_month": "2005-07-01T00:00:00.000Z", "flight_count": 175 }, { "dep_month": "2005-06-01T00:00:00.000Z", "flight_count": 148 }, { "dep_month": "2005-05-01T00:00:00.000Z", "flight_count": 138 }, { "dep_month": "2005-04-01T00:00:00.000Z", "flight_count": 146 }, { "dep_month": "2005-03-01T00:00:00.000Z", "flight_count": 139 }, { "dep_month": "2005-02-01T00:00:00.000Z", "flight_count": 124 }, { "dep_month": "2005-01-01T00:00:00.000Z", "flight_count": 146 }, { "dep_month": "2004-12-01T00:00:00.000Z", "flight_count": 127 }, { "dep_month": "2004-11-01T00:00:00.000Z", "flight_count": 169 }, { "dep_month": "2004-10-01T00:00:00.000Z", "flight_count": 152 }, { "dep_month": "2004-09-01T00:00:00.000Z", "flight_count": 157 }, { "dep_month": "2004-08-01T00:00:00.000Z", "flight_count": 136 }, { "dep_month": "2004-07-01T00:00:00.000Z", "flight_count": 153 }, { "dep_month": "2004-06-01T00:00:00.000Z", "flight_count": 163 }, { "dep_month": "2004-05-01T00:00:00.000Z", "flight_count": 133 }, { "dep_month": "2004-04-01T00:00:00.000Z", "flight_count": 140 }, { "dep_month": "2004-03-01T00:00:00.000Z", "flight_count": 144 }, { "dep_month": "2004-02-01T00:00:00.000Z", "flight_count": 139 }, { "dep_month": "2004-01-01T00:00:00.000Z", "flight_count": 126 }, { "dep_month": "2003-12-01T00:00:00.000Z", "flight_count": 142 }, { "dep_month": "2003-11-01T00:00:00.000Z", "flight_count": 135 }, { "dep_month": "2003-10-01T00:00:00.000Z", "flight_count": 131 }, { "dep_month": "2003-09-01T00:00:00.000Z", "flight_count": 141 }, { "dep_month": "2003-08-01T00:00:00.000Z", "flight_count": 151 }, { "dep_month": "2003-07-01T00:00:00.000Z", "flight_count": 137 }, { "dep_month": "2003-06-01T00:00:00.000Z", "flight_count": 135 }, { "dep_month": "2003-05-01T00:00:00.000Z", "flight_count": 157 }, { "dep_month": "2003-04-01T00:00:00.000Z", "flight_count": 162 }, { "dep_month": "2003-03-01T00:00:00.000Z", "flight_count": 152 }, { "dep_month": "2003-02-01T00:00:00.000Z", "flight_count": 173 }, { "dep_month": "2003-01-01T00:00:00.000Z", "flight_count": 180 }, { "dep_month": "2002-12-01T00:00:00.000Z", "flight_count": 164 }, { "dep_month": "2002-11-01T00:00:00.000Z", "flight_count": 154 }, { "dep_month": "2002-10-01T00:00:00.000Z", "flight_count": 160 }, { "dep_month": "2002-09-01T00:00:00.000Z", "flight_count": 164 }, { "dep_month": "2002-08-01T00:00:00.000Z", "flight_count": 172 }, { "dep_month": "2002-07-01T00:00:00.000Z", "flight_count": 132 }, { "dep_month": "2002-06-01T00:00:00.000Z", "flight_count": 166 }, { "dep_month": "2002-05-01T00:00:00.000Z", "flight_count": 140 }, { "dep_month": "2002-04-01T00:00:00.000Z", "flight_count": 105 }, { "dep_month": "2002-03-01T00:00:00.000Z", "flight_count": 130 }, { "dep_month": "2002-02-01T00:00:00.000Z", "flight_count": 129 }, { "dep_month": "2002-01-01T00:00:00.000Z", "flight_count": 125 }, { "dep_month": "2001-12-01T00:00:00.000Z", "flight_count": 141 }, { "dep_month": "2001-11-01T00:00:00.000Z", "flight_count": 124 }, { "dep_month": "2001-10-01T00:00:00.000Z", "flight_count": 142 }, { "dep_month": "2001-09-01T00:00:00.000Z", "flight_count": 135 }, { "dep_month": "2001-08-01T00:00:00.000Z", "flight_count": 169 }, { "dep_month": "2001-07-01T00:00:00.000Z", "flight_count": 136 }, { "dep_month": "2001-06-01T00:00:00.000Z", "flight_count": 160 }, { "dep_month": "2001-05-01T00:00:00.000Z", "flight_count": 159 }, { "dep_month": "2001-04-01T00:00:00.000Z", "flight_count": 174 }, { "dep_month": "2001-03-01T00:00:00.000Z", "flight_count": 152 }, { "dep_month": "2001-02-01T00:00:00.000Z", "flight_count": 161 }, { "dep_month": "2001-01-01T00:00:00.000Z", "flight_count": 168 }, { "dep_month": "2000-12-01T00:00:00.000Z", "flight_count": 156 }, { "dep_month": "2000-11-01T00:00:00.000Z", "flight_count": 160 }, { "dep_month": "2000-10-01T00:00:00.000Z", "flight_count": 172 }, { "dep_month": "2000-09-01T00:00:00.000Z", "flight_count": 156 }, { "dep_month": "2000-08-01T00:00:00.000Z", "flight_count": 221 }, { "dep_month": "2000-07-01T00:00:00.000Z", "flight_count": 180 }, { "dep_month": "2000-06-01T00:00:00.000Z", "flight_count": 155 }, { "dep_month": "2000-05-01T00:00:00.000Z", "flight_count": 183 }, { "dep_month": "2000-04-01T00:00:00.000Z", "flight_count": 172 }, { "dep_month": "2000-03-01T00:00:00.000Z", "flight_count": 196 }, { "dep_month": "2000-02-01T00:00:00.000Z", "flight_count": 189 }, { "dep_month": "2000-01-01T00:00:00.000Z", "flight_count": 189 } ] }, { "origin_name": "OAK - OAKLAND", "flight_count": 5076, "total_distance": "3472237", "percent_of_flights": 0.12480944184902877, "destination_name": [ { "destination_name": "LAX - LOS ANGELES", "flight_count": 792, "total_distance": "266904", "percent_of_flights": 0.15602836879432624, "cl": [ { "nickname": "Southwest", "flight_count": 745 }, { "nickname": "United", "flight_count": 36 }, { "nickname": "American", "flight_count": 11 } ] }, { "destination_name": "SAN - SAN DIEGO", "flight_count": 479, "total_distance": "213634", "percent_of_flights": 0.09436564223798266, "cl": [ { "nickname": "Southwest", "flight_count": 479 } ] }, { "destination_name": "SEA - SEATTLE", "flight_count": 472, "total_distance": "316712", "percent_of_flights": 0.09298660362490149, "cl": [ { "nickname": "Southwest", "flight_count": 271 }, { "nickname": "Alaska", "flight_count": 201 } ] }, { "destination_name": "ONT - ONTARIO", "flight_count": 433, "total_distance": "156313", "percent_of_flights": 0.08530338849487785, "cl": [ { "nickname": "Southwest", "flight_count": 433 } ] }, { "destination_name": "LAS - LAS VEGAS", "flight_count": 422, "total_distance": "171754", "percent_of_flights": 0.08313632781717888, "cl": [ { "nickname": "Southwest", "flight_count": 382 }, { "nickname": "America West", "flight_count": 40 } ] }, { "destination_name": "BUR - BURBANK", "flight_count": 405, "total_distance": "131625", "percent_of_flights": 0.0797872340425532, "cl": [ { "nickname": "Southwest", "flight_count": 405 } ] } ], "carrier_name": [ { "carrier_name": "Southwest", "flight_count": 3964 }, { "carrier_name": "Jetblue", "flight_count": 297 }, { "carrier_name": "United", "flight_count": 249 }, { "carrier_name": "Alaska", "flight_count": 245 }, { "carrier_name": "America West", "flight_count": 164 }, { "carrier_name": "American", "flight_count": 139 }, { "carrier_name": "Delta", "flight_count": 10 }, { "carrier_name": "Continental", "flight_count": 8 } ], "dep_month": [ { "dep_month": "2005-12-01T00:00:00.000Z", "flight_count": 91 }, { "dep_month": "2005-11-01T00:00:00.000Z", "flight_count": 87 }, { "dep_month": "2005-10-01T00:00:00.000Z", "flight_count": 100 }, { "dep_month": "2005-09-01T00:00:00.000Z", "flight_count": 88 }, { "dep_month": "2005-08-01T00:00:00.000Z", "flight_count": 88 }, { "dep_month": "2005-07-01T00:00:00.000Z", "flight_count": 98 }, { "dep_month": "2005-06-01T00:00:00.000Z", "flight_count": 83 }, { "dep_month": "2005-05-01T00:00:00.000Z", "flight_count": 79 }, { "dep_month": "2005-04-01T00:00:00.000Z", "flight_count": 85 }, { "dep_month": "2005-03-01T00:00:00.000Z", "flight_count": 82 }, { "dep_month": "2005-02-01T00:00:00.000Z", "flight_count": 77 }, { "dep_month": "2005-01-01T00:00:00.000Z", "flight_count": 76 }, { "dep_month": "2004-12-01T00:00:00.000Z", "flight_count": 76 }, { "dep_month": "2004-11-01T00:00:00.000Z", "flight_count": 68 }, { "dep_month": "2004-10-01T00:00:00.000Z", "flight_count": 98 }, { "dep_month": "2004-09-01T00:00:00.000Z", "flight_count": 91 }, { "dep_month": "2004-08-01T00:00:00.000Z", "flight_count": 93 }, { "dep_month": "2004-07-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2004-06-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2004-05-01T00:00:00.000Z", "flight_count": 73 }, { "dep_month": "2004-04-01T00:00:00.000Z", "flight_count": 88 }, { "dep_month": "2004-03-01T00:00:00.000Z", "flight_count": 66 }, { "dep_month": "2004-02-01T00:00:00.000Z", "flight_count": 75 }, { "dep_month": "2004-01-01T00:00:00.000Z", "flight_count": 64 }, { "dep_month": "2003-12-01T00:00:00.000Z", "flight_count": 79 }, { "dep_month": "2003-11-01T00:00:00.000Z", "flight_count": 84 }, { "dep_month": "2003-10-01T00:00:00.000Z", "flight_count": 85 }, { "dep_month": "2003-09-01T00:00:00.000Z", "flight_count": 92 }, { "dep_month": "2003-08-01T00:00:00.000Z", "flight_count": 77 }, { "dep_month": "2003-07-01T00:00:00.000Z", "flight_count": 93 }, { "dep_month": "2003-06-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2003-05-01T00:00:00.000Z", "flight_count": 84 }, { "dep_month": "2003-04-01T00:00:00.000Z", "flight_count": 86 }, { "dep_month": "2003-03-01T00:00:00.000Z", "flight_count": 72 }, { "dep_month": "2003-02-01T00:00:00.000Z", "flight_count": 69 }, { "dep_month": "2003-01-01T00:00:00.000Z", "flight_count": 101 }, { "dep_month": "2002-12-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2002-11-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2002-10-01T00:00:00.000Z", "flight_count": 56 }, { "dep_month": "2002-09-01T00:00:00.000Z", "flight_count": 60 }, { "dep_month": "2002-08-01T00:00:00.000Z", "flight_count": 66 }, { "dep_month": "2002-07-01T00:00:00.000Z", "flight_count": 61 }, { "dep_month": "2002-06-01T00:00:00.000Z", "flight_count": 55 }, { "dep_month": "2002-05-01T00:00:00.000Z", "flight_count": 69 }, { "dep_month": "2002-04-01T00:00:00.000Z", "flight_count": 63 }, { "dep_month": "2002-03-01T00:00:00.000Z", "flight_count": 50 }, { "dep_month": "2002-02-01T00:00:00.000Z", "flight_count": 55 }, { "dep_month": "2002-01-01T00:00:00.000Z", "flight_count": 81 }, { "dep_month": "2001-12-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2001-11-01T00:00:00.000Z", "flight_count": 69 }, { "dep_month": "2001-10-01T00:00:00.000Z", "flight_count": 65 }, { "dep_month": "2001-09-01T00:00:00.000Z", "flight_count": 63 }, { "dep_month": "2001-08-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2001-07-01T00:00:00.000Z", "flight_count": 65 }, { "dep_month": "2001-06-01T00:00:00.000Z", "flight_count": 47 }, { "dep_month": "2001-05-01T00:00:00.000Z", "flight_count": 54 }, { "dep_month": "2001-04-01T00:00:00.000Z", "flight_count": 53 }, { "dep_month": "2001-03-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2001-02-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2001-01-01T00:00:00.000Z", "flight_count": 80 }, { "dep_month": "2000-12-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2000-11-01T00:00:00.000Z", "flight_count": 43 }, { "dep_month": "2000-10-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2000-09-01T00:00:00.000Z", "flight_count": 54 }, { "dep_month": "2000-08-01T00:00:00.000Z", "flight_count": 53 }, { "dep_month": "2000-07-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2000-06-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2000-05-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2000-04-01T00:00:00.000Z", "flight_count": 45 }, { "dep_month": "2000-03-01T00:00:00.000Z", "flight_count": 52 }, { "dep_month": "2000-02-01T00:00:00.000Z", "flight_count": 63 }, { "dep_month": "2000-01-01T00:00:00.000Z", "flight_count": 57 } ] }, { "origin_name": "SAN - SAN DIEGO", "flight_count": 5075, "total_distance": "4691341", "percent_of_flights": 0.12478485370051635, "destination_name": [ { "destination_name": "LAS - LAS VEGAS", "flight_count": 508, "total_distance": "131064", "percent_of_flights": 0.10009852216748769, "cl": [ { "nickname": "Southwest", "flight_count": 470 }, { "nickname": "America West", "flight_count": 38 } ] }, { "destination_name": "OAK - OAKLAND", "flight_count": 482, "total_distance": "214972", "percent_of_flights": 0.09497536945812808, "cl": [ { "nickname": "Southwest", "flight_count": 482 } ] }, { "destination_name": "PHX - PHOENIX", "flight_count": 476, "total_distance": "144704", "percent_of_flights": 0.09379310344827586, "cl": [ { "nickname": "Southwest", "flight_count": 400 }, { "nickname": "America West", "flight_count": 76 } ] }, { "destination_name": "ORD - CHICAGO", "flight_count": 468, "total_distance": "806364", "percent_of_flights": 0.09221674876847291, "cl": [ { "nickname": "United", "flight_count": 251 }, { "nickname": "American", "flight_count": 217 } ] }, { "destination_name": "SMF - SACRAMENTO", "flight_count": 428, "total_distance": "205440", "percent_of_flights": 0.08433497536945812, "cl": [ { "nickname": "Southwest", "flight_count": 428 } ] }, { "destination_name": "SJC - SAN JOSE", "flight_count": 423, "total_distance": "176391", "percent_of_flights": 0.08334975369458128, "cl": [ { "nickname": "Southwest", "flight_count": 366 }, { "nickname": "American", "flight_count": 57 } ] } ], "carrier_name": [ { "carrier_name": "Southwest", "flight_count": 2865 }, { "carrier_name": "United", "flight_count": 641 }, { "carrier_name": "American", "flight_count": 589 }, { "carrier_name": "Alaska", "flight_count": 389 }, { "carrier_name": "USAir", "flight_count": 145 }, { "carrier_name": "Northwest", "flight_count": 138 }, { "carrier_name": "Delta", "flight_count": 135 }, { "carrier_name": "America West", "flight_count": 114 }, { "carrier_name": "Jetblue", "flight_count": 56 }, { "carrier_name": "Continental", "flight_count": 3 } ], "dep_month": [ { "dep_month": "2005-12-01T00:00:00.000Z", "flight_count": 89 }, { "dep_month": "2005-11-01T00:00:00.000Z", "flight_count": 63 }, { "dep_month": "2005-10-01T00:00:00.000Z", "flight_count": 76 }, { "dep_month": "2005-09-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2005-08-01T00:00:00.000Z", "flight_count": 69 }, { "dep_month": "2005-07-01T00:00:00.000Z", "flight_count": 98 }, { "dep_month": "2005-06-01T00:00:00.000Z", "flight_count": 75 }, { "dep_month": "2005-05-01T00:00:00.000Z", "flight_count": 65 }, { "dep_month": "2005-04-01T00:00:00.000Z", "flight_count": 75 }, { "dep_month": "2005-03-01T00:00:00.000Z", "flight_count": 88 }, { "dep_month": "2005-02-01T00:00:00.000Z", "flight_count": 69 }, { "dep_month": "2005-01-01T00:00:00.000Z", "flight_count": 76 }, { "dep_month": "2004-12-01T00:00:00.000Z", "flight_count": 90 }, { "dep_month": "2004-11-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2004-10-01T00:00:00.000Z", "flight_count": 72 }, { "dep_month": "2004-09-01T00:00:00.000Z", "flight_count": 72 }, { "dep_month": "2004-08-01T00:00:00.000Z", "flight_count": 73 }, { "dep_month": "2004-07-01T00:00:00.000Z", "flight_count": 53 }, { "dep_month": "2004-06-01T00:00:00.000Z", "flight_count": 65 }, { "dep_month": "2004-05-01T00:00:00.000Z", "flight_count": 83 }, { "dep_month": "2004-04-01T00:00:00.000Z", "flight_count": 76 }, { "dep_month": "2004-03-01T00:00:00.000Z", "flight_count": 67 }, { "dep_month": "2004-02-01T00:00:00.000Z", "flight_count": 72 }, { "dep_month": "2004-01-01T00:00:00.000Z", "flight_count": 71 }, { "dep_month": "2003-12-01T00:00:00.000Z", "flight_count": 80 }, { "dep_month": "2003-11-01T00:00:00.000Z", "flight_count": 82 }, { "dep_month": "2003-10-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2003-09-01T00:00:00.000Z", "flight_count": 85 }, { "dep_month": "2003-08-01T00:00:00.000Z", "flight_count": 75 }, { "dep_month": "2003-07-01T00:00:00.000Z", "flight_count": 80 }, { "dep_month": "2003-06-01T00:00:00.000Z", "flight_count": 75 }, { "dep_month": "2003-05-01T00:00:00.000Z", "flight_count": 65 }, { "dep_month": "2003-04-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2003-03-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2003-02-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2003-01-01T00:00:00.000Z", "flight_count": 81 }, { "dep_month": "2002-12-01T00:00:00.000Z", "flight_count": 93 }, { "dep_month": "2002-11-01T00:00:00.000Z", "flight_count": 63 }, { "dep_month": "2002-10-01T00:00:00.000Z", "flight_count": 86 }, { "dep_month": "2002-09-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2002-08-01T00:00:00.000Z", "flight_count": 93 }, { "dep_month": "2002-07-01T00:00:00.000Z", "flight_count": 66 }, { "dep_month": "2002-06-01T00:00:00.000Z", "flight_count": 73 }, { "dep_month": "2002-05-01T00:00:00.000Z", "flight_count": 82 }, { "dep_month": "2002-04-01T00:00:00.000Z", "flight_count": 67 }, { "dep_month": "2002-03-01T00:00:00.000Z", "flight_count": 60 }, { "dep_month": "2002-02-01T00:00:00.000Z", "flight_count": 54 }, { "dep_month": "2002-01-01T00:00:00.000Z", "flight_count": 77 }, { "dep_month": "2001-12-01T00:00:00.000Z", "flight_count": 79 }, { "dep_month": "2001-11-01T00:00:00.000Z", "flight_count": 72 }, { "dep_month": "2001-10-01T00:00:00.000Z", "flight_count": 64 }, { "dep_month": "2001-09-01T00:00:00.000Z", "flight_count": 61 }, { "dep_month": "2001-08-01T00:00:00.000Z", "flight_count": 87 }, { "dep_month": "2001-07-01T00:00:00.000Z", "flight_count": 67 }, { "dep_month": "2001-06-01T00:00:00.000Z", "flight_count": 73 }, { "dep_month": "2001-05-01T00:00:00.000Z", "flight_count": 68 }, { "dep_month": "2001-04-01T00:00:00.000Z", "flight_count": 67 }, { "dep_month": "2001-03-01T00:00:00.000Z", "flight_count": 44 }, { "dep_month": "2001-02-01T00:00:00.000Z", "flight_count": 63 }, { "dep_month": "2001-01-01T00:00:00.000Z", "flight_count": 71 }, { "dep_month": "2000-12-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2000-11-01T00:00:00.000Z", "flight_count": 69 }, { "dep_month": "2000-10-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2000-09-01T00:00:00.000Z", "flight_count": 68 }, { "dep_month": "2000-08-01T00:00:00.000Z", "flight_count": 76 }, { "dep_month": "2000-07-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2000-06-01T00:00:00.000Z", "flight_count": 71 }, { "dep_month": "2000-05-01T00:00:00.000Z", "flight_count": 61 }, { "dep_month": "2000-04-01T00:00:00.000Z", "flight_count": 71 }, { "dep_month": "2000-03-01T00:00:00.000Z", "flight_count": 53 }, { "dep_month": "2000-02-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2000-01-01T00:00:00.000Z", "flight_count": 58 } ] }, { "origin_name": "SFO - SAN FRANCISCO", "flight_count": 4540, "total_distance": "6391762", "percent_of_flights": 0.11163019424637324, "destination_name": [ { "destination_name": "LAS - LAS VEGAS", "flight_count": 431, "total_distance": "178434", "percent_of_flights": 0.09493392070484581, "cl": [ { "nickname": "United", "flight_count": 299 }, { "nickname": "America West", "flight_count": 132 } ] }, { "destination_name": "ORD - CHICAGO", "flight_count": 362, "total_distance": "668252", "percent_of_flights": 0.07973568281938326, "cl": [ { "nickname": "United", "flight_count": 282 }, { "nickname": "American", "flight_count": 80 } ] }, { "destination_name": "LAX - LOS ANGELES", "flight_count": 318, "total_distance": "107166", "percent_of_flights": 0.07004405286343612, "cl": [ { "nickname": "United", "flight_count": 182 }, { "nickname": "American", "flight_count": 93 }, { "nickname": "Alaska", "flight_count": 40 }, { "nickname": "Delta", "flight_count": 3 } ] }, { "destination_name": "DFW - DALLAS-FORT WORTH", "flight_count": 304, "total_distance": "445056", "percent_of_flights": 0.06696035242290749, "cl": [ { "nickname": "American", "flight_count": 252 }, { "nickname": "United", "flight_count": 50 }, { "nickname": "Delta", "flight_count": 2 } ] }, { "destination_name": "MSP - MINNEAPOLIS", "flight_count": 281, "total_distance": "446509", "percent_of_flights": 0.0618942731277533, "cl": [ { "nickname": "Northwest", "flight_count": 281 } ] }, { "destination_name": "PHX - PHOENIX", "flight_count": 259, "total_distance": "168609", "percent_of_flights": 0.057048458149779734, "cl": [ { "nickname": "United", "flight_count": 140 }, { "nickname": "America West", "flight_count": 78 }, { "nickname": "Southwest", "flight_count": 41 } ] } ], "carrier_name": [ { "carrier_name": "United", "flight_count": 2464 }, { "carrier_name": "American", "flight_count": 688 }, { "carrier_name": "Northwest", "flight_count": 522 }, { "carrier_name": "Alaska", "flight_count": 221 }, { "carrier_name": "America West", "flight_count": 210 }, { "carrier_name": "USAir", "flight_count": 157 }, { "carrier_name": "Delta", "flight_count": 134 }, { "carrier_name": "Southwest", "flight_count": 86 }, { "carrier_name": "ATA", "flight_count": 37 }, { "carrier_name": "Continental", "flight_count": 21 } ], "dep_month": [ { "dep_month": "2005-12-01T00:00:00.000Z", "flight_count": 84 }, { "dep_month": "2005-11-01T00:00:00.000Z", "flight_count": 92 }, { "dep_month": "2005-10-01T00:00:00.000Z", "flight_count": 95 }, { "dep_month": "2005-09-01T00:00:00.000Z", "flight_count": 79 }, { "dep_month": "2005-08-01T00:00:00.000Z", "flight_count": 74 }, { "dep_month": "2005-07-01T00:00:00.000Z", "flight_count": 92 }, { "dep_month": "2005-06-01T00:00:00.000Z", "flight_count": 86 }, { "dep_month": "2005-05-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2005-04-01T00:00:00.000Z", "flight_count": 81 }, { "dep_month": "2005-03-01T00:00:00.000Z", "flight_count": 74 }, { "dep_month": "2005-02-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2005-01-01T00:00:00.000Z", "flight_count": 75 }, { "dep_month": "2004-12-01T00:00:00.000Z", "flight_count": 86 }, { "dep_month": "2004-11-01T00:00:00.000Z", "flight_count": 75 }, { "dep_month": "2004-10-01T00:00:00.000Z", "flight_count": 77 }, { "dep_month": "2004-09-01T00:00:00.000Z", "flight_count": 63 }, { "dep_month": "2004-08-01T00:00:00.000Z", "flight_count": 71 }, { "dep_month": "2004-07-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2004-06-01T00:00:00.000Z", "flight_count": 74 }, { "dep_month": "2004-05-01T00:00:00.000Z", "flight_count": 78 }, { "dep_month": "2004-04-01T00:00:00.000Z", "flight_count": 68 }, { "dep_month": "2004-03-01T00:00:00.000Z", "flight_count": 68 }, { "dep_month": "2004-02-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2004-01-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2003-12-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2003-11-01T00:00:00.000Z", "flight_count": 54 }, { "dep_month": "2003-10-01T00:00:00.000Z", "flight_count": 45 }, { "dep_month": "2003-09-01T00:00:00.000Z", "flight_count": 64 }, { "dep_month": "2003-08-01T00:00:00.000Z", "flight_count": 69 }, { "dep_month": "2003-07-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2003-06-01T00:00:00.000Z", "flight_count": 40 }, { "dep_month": "2003-05-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2003-04-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2003-03-01T00:00:00.000Z", "flight_count": 47 }, { "dep_month": "2003-02-01T00:00:00.000Z", "flight_count": 50 }, { "dep_month": "2003-01-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2002-12-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2002-11-01T00:00:00.000Z", "flight_count": 48 }, { "dep_month": "2002-10-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2002-09-01T00:00:00.000Z", "flight_count": 56 }, { "dep_month": "2002-08-01T00:00:00.000Z", "flight_count": 55 }, { "dep_month": "2002-07-01T00:00:00.000Z", "flight_count": 63 }, { "dep_month": "2002-06-01T00:00:00.000Z", "flight_count": 65 }, { "dep_month": "2002-05-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2002-04-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2002-03-01T00:00:00.000Z", "flight_count": 45 }, { "dep_month": "2002-02-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2002-01-01T00:00:00.000Z", "flight_count": 43 }, { "dep_month": "2001-12-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2001-11-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2001-10-01T00:00:00.000Z", "flight_count": 66 }, { "dep_month": "2001-09-01T00:00:00.000Z", "flight_count": 55 }, { "dep_month": "2001-08-01T00:00:00.000Z", "flight_count": 55 }, { "dep_month": "2001-07-01T00:00:00.000Z", "flight_count": 78 }, { "dep_month": "2001-06-01T00:00:00.000Z", "flight_count": 69 }, { "dep_month": "2001-05-01T00:00:00.000Z", "flight_count": 65 }, { "dep_month": "2001-04-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2001-03-01T00:00:00.000Z", "flight_count": 69 }, { "dep_month": "2001-02-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2001-01-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2000-12-01T00:00:00.000Z", "flight_count": 40 }, { "dep_month": "2000-11-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2000-10-01T00:00:00.000Z", "flight_count": 54 }, { "dep_month": "2000-09-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2000-08-01T00:00:00.000Z", "flight_count": 61 }, { "dep_month": "2000-07-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2000-06-01T00:00:00.000Z", "flight_count": 55 }, { "dep_month": "2000-05-01T00:00:00.000Z", "flight_count": 73 }, { "dep_month": "2000-04-01T00:00:00.000Z", "flight_count": 50 }, { "dep_month": "2000-03-01T00:00:00.000Z", "flight_count": 67 }, { "dep_month": "2000-02-01T00:00:00.000Z", "flight_count": 56 }, { "dep_month": "2000-01-01T00:00:00.000Z", "flight_count": 64 } ] }, { "origin_name": "SJC - SAN JOSE", "flight_count": 3825, "total_distance": "2826646", "percent_of_flights": 0.09404966805999508, "destination_name": [ { "destination_name": "LAX - LOS ANGELES", "flight_count": 475, "total_distance": "146300", "percent_of_flights": 0.12418300653594772, "cl": [ { "nickname": "Southwest", "flight_count": 419 }, { "nickname": "American", "flight_count": 56 } ] }, { "destination_name": "SEA - SEATTLE", "flight_count": 368, "total_distance": "256496", "percent_of_flights": 0.09620915032679739, "cl": [ { "nickname": "Alaska", "flight_count": 240 }, { "nickname": "Southwest", "flight_count": 106 }, { "nickname": "American", "flight_count": 22 } ] }, { "destination_name": "SAN - SAN DIEGO", "flight_count": 363, "total_distance": "151371", "percent_of_flights": 0.09490196078431372, "cl": [ { "nickname": "Southwest", "flight_count": 315 }, { "nickname": "American", "flight_count": 48 } ] }, { "destination_name": "PHX - PHOENIX", "flight_count": 314, "total_distance": "194994", "percent_of_flights": 0.08209150326797386, "cl": [ { "nickname": "Southwest", "flight_count": 178 }, { "nickname": "America West", "flight_count": 123 }, { "nickname": "American", "flight_count": 13 } ] }, { "destination_name": "LAS - LAS VEGAS", "flight_count": 303, "total_distance": "116958", "percent_of_flights": 0.0792156862745098, "cl": [ { "nickname": "Southwest", "flight_count": 181 }, { "nickname": "American", "flight_count": 65 }, { "nickname": "America West", "flight_count": 57 } ] }, { "destination_name": "BUR - BURBANK", "flight_count": 240, "total_distance": "71040", "percent_of_flights": 0.06274509803921569, "cl": [ { "nickname": "Southwest", "flight_count": 240 } ] } ], "carrier_name": [ { "carrier_name": "Southwest", "flight_count": 2148 }, { "carrier_name": "American", "flight_count": 562 }, { "carrier_name": "Alaska", "flight_count": 366 }, { "carrier_name": "United", "flight_count": 317 }, { "carrier_name": "America West", "flight_count": 180 }, { "carrier_name": "Northwest", "flight_count": 168 }, { "carrier_name": "Continental", "flight_count": 35 }, { "carrier_name": "Jetblue", "flight_count": 21 }, { "carrier_name": "Delta", "flight_count": 19 }, { "carrier_name": "ATA", "flight_count": 9 } ], "dep_month": [ { "dep_month": "2005-12-01T00:00:00.000Z", "flight_count": 77 }, { "dep_month": "2005-11-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2005-10-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2005-09-01T00:00:00.000Z", "flight_count": 54 }, { "dep_month": "2005-08-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2005-07-01T00:00:00.000Z", "flight_count": 65 }, { "dep_month": "2005-06-01T00:00:00.000Z", "flight_count": 64 }, { "dep_month": "2005-05-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2005-04-01T00:00:00.000Z", "flight_count": 56 }, { "dep_month": "2005-03-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2005-02-01T00:00:00.000Z", "flight_count": 60 }, { "dep_month": "2005-01-01T00:00:00.000Z", "flight_count": 55 }, { "dep_month": "2004-12-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2004-11-01T00:00:00.000Z", "flight_count": 78 }, { "dep_month": "2004-10-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2004-09-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2004-08-01T00:00:00.000Z", "flight_count": 60 }, { "dep_month": "2004-07-01T00:00:00.000Z", "flight_count": 44 }, { "dep_month": "2004-06-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2004-05-01T00:00:00.000Z", "flight_count": 48 }, { "dep_month": "2004-04-01T00:00:00.000Z", "flight_count": 39 }, { "dep_month": "2004-03-01T00:00:00.000Z", "flight_count": 42 }, { "dep_month": "2004-02-01T00:00:00.000Z", "flight_count": 50 }, { "dep_month": "2004-01-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2003-12-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2003-11-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2003-10-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2003-09-01T00:00:00.000Z", "flight_count": 56 }, { "dep_month": "2003-08-01T00:00:00.000Z", "flight_count": 56 }, { "dep_month": "2003-07-01T00:00:00.000Z", "flight_count": 43 }, { "dep_month": "2003-06-01T00:00:00.000Z", "flight_count": 48 }, { "dep_month": "2003-05-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2003-04-01T00:00:00.000Z", "flight_count": 44 }, { "dep_month": "2003-03-01T00:00:00.000Z", "flight_count": 45 }, { "dep_month": "2003-02-01T00:00:00.000Z", "flight_count": 43 }, { "dep_month": "2003-01-01T00:00:00.000Z", "flight_count": 54 }, { "dep_month": "2002-12-01T00:00:00.000Z", "flight_count": 47 }, { "dep_month": "2002-11-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2002-10-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2002-09-01T00:00:00.000Z", "flight_count": 60 }, { "dep_month": "2002-08-01T00:00:00.000Z", "flight_count": 64 }, { "dep_month": "2002-07-01T00:00:00.000Z", "flight_count": 45 }, { "dep_month": "2002-06-01T00:00:00.000Z", "flight_count": 64 }, { "dep_month": "2002-05-01T00:00:00.000Z", "flight_count": 73 }, { "dep_month": "2002-04-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2002-03-01T00:00:00.000Z", "flight_count": 60 }, { "dep_month": "2002-02-01T00:00:00.000Z", "flight_count": 71 }, { "dep_month": "2002-01-01T00:00:00.000Z", "flight_count": 54 }, { "dep_month": "2001-12-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2001-11-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2001-10-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2001-09-01T00:00:00.000Z", "flight_count": 47 }, { "dep_month": "2001-08-01T00:00:00.000Z", "flight_count": 69 }, { "dep_month": "2001-07-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2001-06-01T00:00:00.000Z", "flight_count": 61 }, { "dep_month": "2001-05-01T00:00:00.000Z", "flight_count": 53 }, { "dep_month": "2001-04-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2001-03-01T00:00:00.000Z", "flight_count": 40 }, { "dep_month": "2001-02-01T00:00:00.000Z", "flight_count": 50 }, { "dep_month": "2001-01-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2000-12-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2000-11-01T00:00:00.000Z", "flight_count": 44 }, { "dep_month": "2000-10-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2000-09-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2000-08-01T00:00:00.000Z", "flight_count": 53 }, { "dep_month": "2000-07-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2000-06-01T00:00:00.000Z", "flight_count": 38 }, { "dep_month": "2000-05-01T00:00:00.000Z", "flight_count": 60 }, { "dep_month": "2000-04-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2000-03-01T00:00:00.000Z", "flight_count": 35 }, { "dep_month": "2000-02-01T00:00:00.000Z", "flight_count": 52 }, { "dep_month": "2000-01-01T00:00:00.000Z", "flight_count": 53 } ] }, { "origin_name": "SMF - SACRAMENTO", "flight_count": 3576, "total_distance": "2367376", "percent_of_flights": 0.08792721908040324, "destination_name": [ { "destination_name": "PHX - PHOENIX", "flight_count": 479, "total_distance": "309913", "percent_of_flights": 0.13394854586129754, "cl": [ { "nickname": "America West", "flight_count": 249 }, { "nickname": "Southwest", "flight_count": 230 } ] }, { "destination_name": "SAN - SAN DIEGO", "flight_count": 396, "total_distance": "190080", "percent_of_flights": 0.11073825503355705, "cl": [ { "nickname": "Southwest", "flight_count": 396 } ] }, { "destination_name": "LAX - LOS ANGELES", "flight_count": 382, "total_distance": "142486", "percent_of_flights": 0.10682326621923938, "cl": [ { "nickname": "Southwest", "flight_count": 345 }, { "nickname": "United", "flight_count": 37 } ] }, { "destination_name": "ONT - ONTARIO", "flight_count": 380, "total_distance": "147820", "percent_of_flights": 0.10626398210290827, "cl": [ { "nickname": "Southwest", "flight_count": 380 } ] }, { "destination_name": "LAS - LAS VEGAS", "flight_count": 327, "total_distance": "129819", "percent_of_flights": 0.09144295302013423, "cl": [ { "nickname": "Southwest", "flight_count": 269 }, { "nickname": "America West", "flight_count": 58 } ] }, { "destination_name": "BUR - BURBANK", "flight_count": 279, "total_distance": "99882", "percent_of_flights": 0.07802013422818792, "cl": [ { "nickname": "Southwest", "flight_count": 279 } ] } ], "carrier_name": [ { "carrier_name": "Southwest", "flight_count": 2471 }, { "carrier_name": "America West", "flight_count": 376 }, { "carrier_name": "United", "flight_count": 281 }, { "carrier_name": "American", "flight_count": 138 }, { "carrier_name": "Alaska", "flight_count": 132 }, { "carrier_name": "Northwest", "flight_count": 110 }, { "carrier_name": "Delta", "flight_count": 29 }, { "carrier_name": "Continental", "flight_count": 21 }, { "carrier_name": "Jetblue", "flight_count": 18 } ], "dep_month": [ { "dep_month": "2005-12-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2005-11-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2005-10-01T00:00:00.000Z", "flight_count": 53 }, { "dep_month": "2005-09-01T00:00:00.000Z", "flight_count": 62 }, { "dep_month": "2005-08-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2005-07-01T00:00:00.000Z", "flight_count": 64 }, { "dep_month": "2005-06-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2005-05-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2005-04-01T00:00:00.000Z", "flight_count": 56 }, { "dep_month": "2005-03-01T00:00:00.000Z", "flight_count": 55 }, { "dep_month": "2005-02-01T00:00:00.000Z", "flight_count": 63 }, { "dep_month": "2005-01-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2004-12-01T00:00:00.000Z", "flight_count": 52 }, { "dep_month": "2004-11-01T00:00:00.000Z", "flight_count": 70 }, { "dep_month": "2004-10-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2004-09-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2004-08-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2004-07-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2004-06-01T00:00:00.000Z", "flight_count": 53 }, { "dep_month": "2004-05-01T00:00:00.000Z", "flight_count": 67 }, { "dep_month": "2004-04-01T00:00:00.000Z", "flight_count": 47 }, { "dep_month": "2004-03-01T00:00:00.000Z", "flight_count": 41 }, { "dep_month": "2004-02-01T00:00:00.000Z", "flight_count": 60 }, { "dep_month": "2004-01-01T00:00:00.000Z", "flight_count": 47 }, { "dep_month": "2003-12-01T00:00:00.000Z", "flight_count": 48 }, { "dep_month": "2003-11-01T00:00:00.000Z", "flight_count": 40 }, { "dep_month": "2003-10-01T00:00:00.000Z", "flight_count": 50 }, { "dep_month": "2003-09-01T00:00:00.000Z", "flight_count": 56 }, { "dep_month": "2003-08-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2003-07-01T00:00:00.000Z", "flight_count": 41 }, { "dep_month": "2003-06-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2003-05-01T00:00:00.000Z", "flight_count": 60 }, { "dep_month": "2003-04-01T00:00:00.000Z", "flight_count": 52 }, { "dep_month": "2003-03-01T00:00:00.000Z", "flight_count": 31 }, { "dep_month": "2003-02-01T00:00:00.000Z", "flight_count": 44 }, { "dep_month": "2003-01-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2002-12-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2002-11-01T00:00:00.000Z", "flight_count": 45 }, { "dep_month": "2002-10-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2002-09-01T00:00:00.000Z", "flight_count": 53 }, { "dep_month": "2002-08-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2002-07-01T00:00:00.000Z", "flight_count": 68 }, { "dep_month": "2002-06-01T00:00:00.000Z", "flight_count": 54 }, { "dep_month": "2002-05-01T00:00:00.000Z", "flight_count": 47 }, { "dep_month": "2002-04-01T00:00:00.000Z", "flight_count": 37 }, { "dep_month": "2002-03-01T00:00:00.000Z", "flight_count": 59 }, { "dep_month": "2002-02-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2002-01-01T00:00:00.000Z", "flight_count": 54 }, { "dep_month": "2001-12-01T00:00:00.000Z", "flight_count": 48 }, { "dep_month": "2001-11-01T00:00:00.000Z", "flight_count": 43 }, { "dep_month": "2001-10-01T00:00:00.000Z", "flight_count": 53 }, { "dep_month": "2001-09-01T00:00:00.000Z", "flight_count": 39 }, { "dep_month": "2001-08-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2001-07-01T00:00:00.000Z", "flight_count": 45 }, { "dep_month": "2001-06-01T00:00:00.000Z", "flight_count": 39 }, { "dep_month": "2001-05-01T00:00:00.000Z", "flight_count": 41 }, { "dep_month": "2001-04-01T00:00:00.000Z", "flight_count": 44 }, { "dep_month": "2001-03-01T00:00:00.000Z", "flight_count": 29 }, { "dep_month": "2001-02-01T00:00:00.000Z", "flight_count": 40 }, { "dep_month": "2001-01-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2000-12-01T00:00:00.000Z", "flight_count": 39 }, { "dep_month": "2000-11-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2000-10-01T00:00:00.000Z", "flight_count": 40 }, { "dep_month": "2000-09-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2000-08-01T00:00:00.000Z", "flight_count": 50 }, { "dep_month": "2000-07-01T00:00:00.000Z", "flight_count": 44 }, { "dep_month": "2000-06-01T00:00:00.000Z", "flight_count": 40 }, { "dep_month": "2000-05-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2000-04-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2000-03-01T00:00:00.000Z", "flight_count": 55 }, { "dep_month": "2000-02-01T00:00:00.000Z", "flight_count": 43 }, { "dep_month": "2000-01-01T00:00:00.000Z", "flight_count": 47 } ] }, { "origin_name": "ONT - ONTARIO", "flight_count": 2642, "total_distance": "1492508", "percent_of_flights": 0.06496188836980575, "destination_name": [ { "destination_name": "PHX - PHOENIX", "flight_count": 491, "total_distance": "159575", "percent_of_flights": 0.1858440575321726, "cl": [ { "nickname": "Southwest", "flight_count": 380 }, { "nickname": "America West", "flight_count": 111 } ] }, { "destination_name": "SMF - SACRAMENTO", "flight_count": 412, "total_distance": "160268", "percent_of_flights": 0.15594246782740348, "cl": [ { "nickname": "Southwest", "flight_count": 412 } ] }, { "destination_name": "OAK - OAKLAND", "flight_count": 411, "total_distance": "148371", "percent_of_flights": 0.15556396669190006, "cl": [ { "nickname": "Southwest", "flight_count": 411 } ] }, { "destination_name": "LAS - LAS VEGAS", "flight_count": 344, "total_distance": "67768", "percent_of_flights": 0.13020439061317185, "cl": [ { "nickname": "Southwest", "flight_count": 323 }, { "nickname": "America West", "flight_count": 21 } ] }, { "destination_name": "SJC - SAN JOSE", "flight_count": 236, "total_distance": "78588", "percent_of_flights": 0.08932626797880394, "cl": [ { "nickname": "Southwest", "flight_count": 236 } ] }, { "destination_name": "SEA - SEATTLE", "flight_count": 145, "total_distance": "138620", "percent_of_flights": 0.05488266464799394, "cl": [ { "nickname": "Alaska", "flight_count": 145 } ] } ], "carrier_name": [ { "carrier_name": "Southwest", "flight_count": 1804 }, { "carrier_name": "Alaska", "flight_count": 236 }, { "carrier_name": "United", "flight_count": 153 }, { "carrier_name": "American", "flight_count": 142 }, { "carrier_name": "America West", "flight_count": 132 }, { "carrier_name": "Northwest", "flight_count": 105 }, { "carrier_name": "Jetblue", "flight_count": 31 }, { "carrier_name": "Delta", "flight_count": 27 }, { "carrier_name": "Continental", "flight_count": 11 }, { "carrier_name": "Continental Express", "flight_count": 1 } ], "dep_month": [ { "dep_month": "2005-12-01T00:00:00.000Z", "flight_count": 48 }, { "dep_month": "2005-11-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2005-10-01T00:00:00.000Z", "flight_count": 42 }, { "dep_month": "2005-09-01T00:00:00.000Z", "flight_count": 58 }, { "dep_month": "2005-08-01T00:00:00.000Z", "flight_count": 42 }, { "dep_month": "2005-07-01T00:00:00.000Z", "flight_count": 41 }, { "dep_month": "2005-06-01T00:00:00.000Z", "flight_count": 35 }, { "dep_month": "2005-05-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2005-04-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2005-03-01T00:00:00.000Z", "flight_count": 38 }, { "dep_month": "2005-02-01T00:00:00.000Z", "flight_count": 44 }, { "dep_month": "2005-01-01T00:00:00.000Z", "flight_count": 52 }, { "dep_month": "2004-12-01T00:00:00.000Z", "flight_count": 48 }, { "dep_month": "2004-11-01T00:00:00.000Z", "flight_count": 57 }, { "dep_month": "2004-10-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2004-09-01T00:00:00.000Z", "flight_count": 28 }, { "dep_month": "2004-08-01T00:00:00.000Z", "flight_count": 45 }, { "dep_month": "2004-07-01T00:00:00.000Z", "flight_count": 20 }, { "dep_month": "2004-06-01T00:00:00.000Z", "flight_count": 32 }, { "dep_month": "2004-05-01T00:00:00.000Z", "flight_count": 44 }, { "dep_month": "2004-04-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2004-03-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2004-02-01T00:00:00.000Z", "flight_count": 31 }, { "dep_month": "2004-01-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2003-12-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2003-11-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2003-10-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2003-09-01T00:00:00.000Z", "flight_count": 38 }, { "dep_month": "2003-08-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2003-07-01T00:00:00.000Z", "flight_count": 51 }, { "dep_month": "2003-06-01T00:00:00.000Z", "flight_count": 31 }, { "dep_month": "2003-05-01T00:00:00.000Z", "flight_count": 38 }, { "dep_month": "2003-04-01T00:00:00.000Z", "flight_count": 31 }, { "dep_month": "2003-03-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2003-02-01T00:00:00.000Z", "flight_count": 25 }, { "dep_month": "2003-01-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2002-12-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2002-11-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2002-10-01T00:00:00.000Z", "flight_count": 42 }, { "dep_month": "2002-09-01T00:00:00.000Z", "flight_count": 44 }, { "dep_month": "2002-08-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2002-07-01T00:00:00.000Z", "flight_count": 37 }, { "dep_month": "2002-06-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2002-05-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2002-04-01T00:00:00.000Z", "flight_count": 31 }, { "dep_month": "2002-03-01T00:00:00.000Z", "flight_count": 32 }, { "dep_month": "2002-02-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2002-01-01T00:00:00.000Z", "flight_count": 40 }, { "dep_month": "2001-12-01T00:00:00.000Z", "flight_count": 35 }, { "dep_month": "2001-11-01T00:00:00.000Z", "flight_count": 41 }, { "dep_month": "2001-10-01T00:00:00.000Z", "flight_count": 39 }, { "dep_month": "2001-09-01T00:00:00.000Z", "flight_count": 32 }, { "dep_month": "2001-08-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2001-07-01T00:00:00.000Z", "flight_count": 39 }, { "dep_month": "2001-06-01T00:00:00.000Z", "flight_count": 40 }, { "dep_month": "2001-05-01T00:00:00.000Z", "flight_count": 47 }, { "dep_month": "2001-04-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2001-03-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2001-02-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2001-01-01T00:00:00.000Z", "flight_count": 42 }, { "dep_month": "2000-12-01T00:00:00.000Z", "flight_count": 37 }, { "dep_month": "2000-11-01T00:00:00.000Z", "flight_count": 29 }, { "dep_month": "2000-10-01T00:00:00.000Z", "flight_count": 37 }, { "dep_month": "2000-09-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2000-08-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2000-07-01T00:00:00.000Z", "flight_count": 26 }, { "dep_month": "2000-06-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2000-05-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2000-04-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2000-03-01T00:00:00.000Z", "flight_count": 28 }, { "dep_month": "2000-02-01T00:00:00.000Z", "flight_count": 45 }, { "dep_month": "2000-01-01T00:00:00.000Z", "flight_count": 36 } ] }, { "origin_name": "BUR - BURBANK", "flight_count": 1952, "total_distance": "876479", "percent_of_flights": 0.04799606589623801, "destination_name": [ { "destination_name": "LAS - LAS VEGAS", "flight_count": 388, "total_distance": "86524", "percent_of_flights": 0.1987704918032787, "cl": [ { "nickname": "Southwest", "flight_count": 384 }, { "nickname": "America West", "flight_count": 4 } ] }, { "destination_name": "OAK - OAKLAND", "flight_count": 367, "total_distance": "119275", "percent_of_flights": 0.1880122950819672, "cl": [ { "nickname": "Southwest", "flight_count": 367 } ] }, { "destination_name": "PHX - PHOENIX", "flight_count": 357, "total_distance": "131733", "percent_of_flights": 0.18288934426229508, "cl": [ { "nickname": "Southwest", "flight_count": 260 }, { "nickname": "America West", "flight_count": 97 } ] }, { "destination_name": "SMF - SACRAMENTO", "flight_count": 260, "total_distance": "93080", "percent_of_flights": 0.13319672131147542, "cl": [ { "nickname": "Southwest", "flight_count": 260 } ] }, { "destination_name": "SJC - SAN JOSE", "flight_count": 206, "total_distance": "60976", "percent_of_flights": 0.10553278688524591, "cl": [ { "nickname": "Southwest", "flight_count": 206 } ] }, { "destination_name": "SEA - SEATTLE", "flight_count": 154, "total_distance": "144298", "percent_of_flights": 0.07889344262295082, "cl": [ { "nickname": "Alaska", "flight_count": 154 } ] } ], "carrier_name": [ { "carrier_name": "Southwest", "flight_count": 1477 }, { "carrier_name": "Alaska", "flight_count": 248 }, { "carrier_name": "America West", "flight_count": 101 }, { "carrier_name": "American", "flight_count": 96 }, { "carrier_name": "Jetblue", "flight_count": 16 }, { "carrier_name": "United", "flight_count": 13 }, { "carrier_name": "Delta", "flight_count": 1 } ], "dep_month": [ { "dep_month": "2005-12-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2005-11-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2005-10-01T00:00:00.000Z", "flight_count": 37 }, { "dep_month": "2005-09-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2005-08-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2005-07-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2005-06-01T00:00:00.000Z", "flight_count": 19 }, { "dep_month": "2005-05-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2005-04-01T00:00:00.000Z", "flight_count": 35 }, { "dep_month": "2005-03-01T00:00:00.000Z", "flight_count": 42 }, { "dep_month": "2005-02-01T00:00:00.000Z", "flight_count": 49 }, { "dep_month": "2005-01-01T00:00:00.000Z", "flight_count": 28 }, { "dep_month": "2004-12-01T00:00:00.000Z", "flight_count": 31 }, { "dep_month": "2004-11-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2004-10-01T00:00:00.000Z", "flight_count": 28 }, { "dep_month": "2004-09-01T00:00:00.000Z", "flight_count": 39 }, { "dep_month": "2004-08-01T00:00:00.000Z", "flight_count": 25 }, { "dep_month": "2004-07-01T00:00:00.000Z", "flight_count": 19 }, { "dep_month": "2004-06-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2004-05-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2004-04-01T00:00:00.000Z", "flight_count": 21 }, { "dep_month": "2004-03-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2004-02-01T00:00:00.000Z", "flight_count": 29 }, { "dep_month": "2004-01-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2003-12-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2003-11-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2003-10-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2003-09-01T00:00:00.000Z", "flight_count": 28 }, { "dep_month": "2003-08-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2003-07-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2003-06-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2003-05-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2003-04-01T00:00:00.000Z", "flight_count": 37 }, { "dep_month": "2003-03-01T00:00:00.000Z", "flight_count": 19 }, { "dep_month": "2003-02-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2003-01-01T00:00:00.000Z", "flight_count": 25 }, { "dep_month": "2002-12-01T00:00:00.000Z", "flight_count": 31 }, { "dep_month": "2002-11-01T00:00:00.000Z", "flight_count": 26 }, { "dep_month": "2002-10-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2002-09-01T00:00:00.000Z", "flight_count": 26 }, { "dep_month": "2002-08-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2002-07-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2002-06-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2002-05-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2002-04-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2002-03-01T00:00:00.000Z", "flight_count": 29 }, { "dep_month": "2002-02-01T00:00:00.000Z", "flight_count": 19 }, { "dep_month": "2002-01-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2001-12-01T00:00:00.000Z", "flight_count": 21 }, { "dep_month": "2001-11-01T00:00:00.000Z", "flight_count": 32 }, { "dep_month": "2001-10-01T00:00:00.000Z", "flight_count": 26 }, { "dep_month": "2001-09-01T00:00:00.000Z", "flight_count": 16 }, { "dep_month": "2001-08-01T00:00:00.000Z", "flight_count": 25 }, { "dep_month": "2001-07-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2001-06-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2001-05-01T00:00:00.000Z", "flight_count": 25 }, { "dep_month": "2001-04-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2001-03-01T00:00:00.000Z", "flight_count": 18 }, { "dep_month": "2001-02-01T00:00:00.000Z", "flight_count": 41 }, { "dep_month": "2001-01-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2000-12-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2000-11-01T00:00:00.000Z", "flight_count": 25 }, { "dep_month": "2000-10-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2000-09-01T00:00:00.000Z", "flight_count": 17 }, { "dep_month": "2000-08-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2000-07-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2000-06-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2000-05-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2000-04-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2000-03-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2000-02-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2000-01-01T00:00:00.000Z", "flight_count": 24 } ] }, { "origin_name": "SNA - SANTA ANA", "flight_count": 1919, "total_distance": "1377110", "percent_of_flights": 0.04718465699532825, "destination_name": [ { "destination_name": "PHX - PHOENIX", "flight_count": 297, "total_distance": "100386", "percent_of_flights": 0.15476810838978636, "cl": [ { "nickname": "America West", "flight_count": 174 }, { "nickname": "Southwest", "flight_count": 123 } ] }, { "destination_name": "LAS - LAS VEGAS", "flight_count": 260, "total_distance": "58760", "percent_of_flights": 0.1354872329338197, "cl": [ { "nickname": "America West", "flight_count": 157 }, { "nickname": "Southwest", "flight_count": 100 }, { "nickname": "American", "flight_count": 2 }, { "nickname": "Delta", "flight_count": 1 } ] }, { "destination_name": "SMF - SACRAMENTO", "flight_count": 252, "total_distance": "101808", "percent_of_flights": 0.13131839499739448, "cl": [ { "nickname": "Southwest", "flight_count": 163 }, { "nickname": "America West", "flight_count": 89 } ] }, { "destination_name": "SFO - SAN FRANCISCO", "flight_count": 177, "total_distance": "65844", "percent_of_flights": 0.09223553934340803, "cl": [ { "nickname": "United", "flight_count": 171 }, { "nickname": "American", "flight_count": 6 } ] }, { "destination_name": "DEN - DENVER", "flight_count": 173, "total_distance": "146358", "percent_of_flights": 0.09015112037519542, "cl": [ { "nickname": "United", "flight_count": 173 } ] }, { "destination_name": "OAK - OAKLAND", "flight_count": 160, "total_distance": "59360", "percent_of_flights": 0.08337675872850443, "cl": [ { "nickname": "Southwest", "flight_count": 160 } ] } ], "carrier_name": [ { "carrier_name": "Southwest", "flight_count": 658 }, { "carrier_name": "United", "flight_count": 460 }, { "carrier_name": "America West", "flight_count": 420 }, { "carrier_name": "Northwest", "flight_count": 133 }, { "carrier_name": "USAir", "flight_count": 105 }, { "carrier_name": "American", "flight_count": 92 }, { "carrier_name": "Delta", "flight_count": 51 } ], "dep_month": [ { "dep_month": "2005-12-01T00:00:00.000Z", "flight_count": 40 }, { "dep_month": "2005-11-01T00:00:00.000Z", "flight_count": 37 }, { "dep_month": "2005-10-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2005-09-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2005-08-01T00:00:00.000Z", "flight_count": 37 }, { "dep_month": "2005-07-01T00:00:00.000Z", "flight_count": 39 }, { "dep_month": "2005-06-01T00:00:00.000Z", "flight_count": 32 }, { "dep_month": "2005-05-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2005-04-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2005-03-01T00:00:00.000Z", "flight_count": 35 }, { "dep_month": "2005-02-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2005-01-01T00:00:00.000Z", "flight_count": 26 }, { "dep_month": "2004-12-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2004-11-01T00:00:00.000Z", "flight_count": 43 }, { "dep_month": "2004-10-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2004-09-01T00:00:00.000Z", "flight_count": 29 }, { "dep_month": "2004-08-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2004-07-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2004-06-01T00:00:00.000Z", "flight_count": 19 }, { "dep_month": "2004-05-01T00:00:00.000Z", "flight_count": 18 }, { "dep_month": "2004-04-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2004-03-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2004-02-01T00:00:00.000Z", "flight_count": 46 }, { "dep_month": "2004-01-01T00:00:00.000Z", "flight_count": 25 }, { "dep_month": "2003-12-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2003-11-01T00:00:00.000Z", "flight_count": 32 }, { "dep_month": "2003-10-01T00:00:00.000Z", "flight_count": 29 }, { "dep_month": "2003-09-01T00:00:00.000Z", "flight_count": 29 }, { "dep_month": "2003-08-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2003-07-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2003-06-01T00:00:00.000Z", "flight_count": 21 }, { "dep_month": "2003-05-01T00:00:00.000Z", "flight_count": 21 }, { "dep_month": "2003-04-01T00:00:00.000Z", "flight_count": 28 }, { "dep_month": "2003-03-01T00:00:00.000Z", "flight_count": 30 }, { "dep_month": "2003-02-01T00:00:00.000Z", "flight_count": 26 }, { "dep_month": "2003-01-01T00:00:00.000Z", "flight_count": 36 }, { "dep_month": "2002-12-01T00:00:00.000Z", "flight_count": 20 }, { "dep_month": "2002-11-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2002-10-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2002-09-01T00:00:00.000Z", "flight_count": 19 }, { "dep_month": "2002-08-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2002-07-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2002-06-01T00:00:00.000Z", "flight_count": 21 }, { "dep_month": "2002-05-01T00:00:00.000Z", "flight_count": 29 }, { "dep_month": "2002-04-01T00:00:00.000Z", "flight_count": 34 }, { "dep_month": "2002-03-01T00:00:00.000Z", "flight_count": 38 }, { "dep_month": "2002-02-01T00:00:00.000Z", "flight_count": 28 }, { "dep_month": "2002-01-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2001-12-01T00:00:00.000Z", "flight_count": 19 }, { "dep_month": "2001-11-01T00:00:00.000Z", "flight_count": 21 }, { "dep_month": "2001-10-01T00:00:00.000Z", "flight_count": 20 }, { "dep_month": "2001-09-01T00:00:00.000Z", "flight_count": 20 }, { "dep_month": "2001-08-01T00:00:00.000Z", "flight_count": 18 }, { "dep_month": "2001-07-01T00:00:00.000Z", "flight_count": 28 }, { "dep_month": "2001-06-01T00:00:00.000Z", "flight_count": 19 }, { "dep_month": "2001-05-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2001-04-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2001-03-01T00:00:00.000Z", "flight_count": 21 }, { "dep_month": "2001-02-01T00:00:00.000Z", "flight_count": 17 }, { "dep_month": "2001-01-01T00:00:00.000Z", "flight_count": 16 }, { "dep_month": "2000-12-01T00:00:00.000Z", "flight_count": 17 }, { "dep_month": "2000-11-01T00:00:00.000Z", "flight_count": 15 }, { "dep_month": "2000-10-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2000-09-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2000-08-01T00:00:00.000Z", "flight_count": 18 }, { "dep_month": "2000-07-01T00:00:00.000Z", "flight_count": 14 }, { "dep_month": "2000-06-01T00:00:00.000Z", "flight_count": 20 }, { "dep_month": "2000-05-01T00:00:00.000Z", "flight_count": 29 }, { "dep_month": "2000-04-01T00:00:00.000Z", "flight_count": 27 }, { "dep_month": "2000-03-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2000-02-01T00:00:00.000Z", "flight_count": 18 }, { "dep_month": "2000-01-01T00:00:00.000Z", "flight_count": 28 } ] }, { "origin_name": "LGB - LONG BEACH", "flight_count": 661, "total_distance": "931914", "percent_of_flights": 0.016252766166707646, "destination_name": [ { "destination_name": "JFK - NEW YORK", "flight_count": 149, "total_distance": "367285", "percent_of_flights": 0.2254160363086233, "cl": [ { "nickname": "Jetblue", "flight_count": 149 } ] }, { "destination_name": "OAK - OAKLAND", "flight_count": 117, "total_distance": "41301", "percent_of_flights": 0.17700453857791226, "cl": [ { "nickname": "Jetblue", "flight_count": 117 } ] }, { "destination_name": "DFW - DALLAS-FORT WORTH", "flight_count": 97, "total_distance": "118340", "percent_of_flights": 0.14674735249621784, "cl": [ { "nickname": "American", "flight_count": 97 } ] }, { "destination_name": "IAD - WASHINGTON", "flight_count": 77, "total_distance": "175329", "percent_of_flights": 0.11649016641452345, "cl": [ { "nickname": "Jetblue", "flight_count": 77 } ] }, { "destination_name": "LAS - LAS VEGAS", "flight_count": 56, "total_distance": "12936", "percent_of_flights": 0.08472012102874432, "cl": [ { "nickname": "Jetblue", "flight_count": 56 } ] }, { "destination_name": "PHX - PHOENIX", "flight_count": 54, "total_distance": "19170", "percent_of_flights": 0.08169440242057488, "cl": [ { "nickname": "America West", "flight_count": 54 } ] } ], "carrier_name": [ { "carrier_name": "Jetblue", "flight_count": 471 }, { "carrier_name": "American", "flight_count": 126 }, { "carrier_name": "America West", "flight_count": 54 }, { "carrier_name": "Alaska", "flight_count": 10 } ], "dep_month": [ { "dep_month": "2005-12-01T00:00:00.000Z", "flight_count": 19 }, { "dep_month": "2005-11-01T00:00:00.000Z", "flight_count": 22 }, { "dep_month": "2005-10-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2005-09-01T00:00:00.000Z", "flight_count": 14 }, { "dep_month": "2005-08-01T00:00:00.000Z", "flight_count": 13 }, { "dep_month": "2005-07-01T00:00:00.000Z", "flight_count": 24 }, { "dep_month": "2005-06-01T00:00:00.000Z", "flight_count": 23 }, { "dep_month": "2005-05-01T00:00:00.000Z", "flight_count": 12 }, { "dep_month": "2005-04-01T00:00:00.000Z", "flight_count": 3 }, { "dep_month": "2005-03-01T00:00:00.000Z", "flight_count": 5 }, { "dep_month": "2005-02-01T00:00:00.000Z", "flight_count": 8 }, { "dep_month": "2005-01-01T00:00:00.000Z", "flight_count": 17 }, { "dep_month": "2004-12-01T00:00:00.000Z", "flight_count": 13 }, { "dep_month": "2004-11-01T00:00:00.000Z", "flight_count": 10 }, { "dep_month": "2004-10-01T00:00:00.000Z", "flight_count": 9 }, { "dep_month": "2004-09-01T00:00:00.000Z", "flight_count": 18 }, { "dep_month": "2004-08-01T00:00:00.000Z", "flight_count": 17 }, { "dep_month": "2004-07-01T00:00:00.000Z", "flight_count": 15 }, { "dep_month": "2004-06-01T00:00:00.000Z", "flight_count": 15 }, { "dep_month": "2004-05-01T00:00:00.000Z", "flight_count": 6 }, { "dep_month": "2004-04-01T00:00:00.000Z", "flight_count": 11 }, { "dep_month": "2004-03-01T00:00:00.000Z", "flight_count": 10 }, { "dep_month": "2004-02-01T00:00:00.000Z", "flight_count": 12 }, { "dep_month": "2004-01-01T00:00:00.000Z", "flight_count": 10 }, { "dep_month": "2003-12-01T00:00:00.000Z", "flight_count": 15 }, { "dep_month": "2003-11-01T00:00:00.000Z", "flight_count": 17 }, { "dep_month": "2003-10-01T00:00:00.000Z", "flight_count": 19 }, { "dep_month": "2003-09-01T00:00:00.000Z", "flight_count": 21 }, { "dep_month": "2003-08-01T00:00:00.000Z", "flight_count": 11 }, { "dep_month": "2003-07-01T00:00:00.000Z", "flight_count": 15 }, { "dep_month": "2003-06-01T00:00:00.000Z", "flight_count": 25 }, { "dep_month": "2003-05-01T00:00:00.000Z", "flight_count": 17 }, { "dep_month": "2003-04-01T00:00:00.000Z", "flight_count": 17 }, { "dep_month": "2003-03-01T00:00:00.000Z", "flight_count": 12 }, { "dep_month": "2003-02-01T00:00:00.000Z", "flight_count": 3 }, { "dep_month": "2003-01-01T00:00:00.000Z", "flight_count": 33 }, { "dep_month": "2002-12-01T00:00:00.000Z", "flight_count": 5 }, { "dep_month": "2002-11-01T00:00:00.000Z", "flight_count": 3 }, { "dep_month": "2002-10-01T00:00:00.000Z", "flight_count": 7 }, { "dep_month": "2002-09-01T00:00:00.000Z", "flight_count": 5 }, { "dep_month": "2002-08-01T00:00:00.000Z", "flight_count": 2 }, { "dep_month": "2002-07-01T00:00:00.000Z", "flight_count": 4 }, { "dep_month": "2002-06-01T00:00:00.000Z", "flight_count": 1 }, { "dep_month": "2002-05-01T00:00:00.000Z", "flight_count": 2 }, { "dep_month": "2002-03-01T00:00:00.000Z", "flight_count": 3 }, { "dep_month": "2002-02-01T00:00:00.000Z", "flight_count": 3 }, { "dep_month": "2002-01-01T00:00:00.000Z", "flight_count": 1 }, { "dep_month": "2001-12-01T00:00:00.000Z", "flight_count": 3 }, { "dep_month": "2001-11-01T00:00:00.000Z", "flight_count": 2 }, { "dep_month": "2001-10-01T00:00:00.000Z", "flight_count": 2 }, { "dep_month": "2001-09-01T00:00:00.000Z", "flight_count": 4 }, { "dep_month": "2001-08-01T00:00:00.000Z", "flight_count": 4 }, { "dep_month": "2001-07-01T00:00:00.000Z", "flight_count": 2 }, { "dep_month": "2001-06-01T00:00:00.000Z", "flight_count": 3 }, { "dep_month": "2001-05-01T00:00:00.000Z", "flight_count": 5 }, { "dep_month": "2001-04-01T00:00:00.000Z", "flight_count": 2 }, { "dep_month": "2001-03-01T00:00:00.000Z", "flight_count": 3 }, { "dep_month": "2001-02-01T00:00:00.000Z", "flight_count": 4 }, { "dep_month": "2001-01-01T00:00:00.000Z", "flight_count": 9 }, { "dep_month": "2000-12-01T00:00:00.000Z", "flight_count": 4 }, { "dep_month": "2000-11-01T00:00:00.000Z", "flight_count": 1 }, { "dep_month": "2000-10-01T00:00:00.000Z", "flight_count": 1 }, { "dep_month": "2000-08-01T00:00:00.000Z", "flight_count": 5 }, { "dep_month": "2000-07-01T00:00:00.000Z", "flight_count": 7 }, { "dep_month": "2000-06-01T00:00:00.000Z", "flight_count": 4 }, { "dep_month": "2000-05-01T00:00:00.000Z", "flight_count": 7 }, { "dep_month": "2000-04-01T00:00:00.000Z", "flight_count": 4 }, { "dep_month": "2000-03-01T00:00:00.000Z", "flight_count": 7 }, { "dep_month": "2000-02-01T00:00:00.000Z", "flight_count": 6 }, { "dep_month": "2000-01-01T00:00:00.000Z", "flight_count": 2 } ] } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set IN (1,2,3,4,5) THEN CONCAT(origin_0."code",' - ',origin_0."city") END as "origin_name__1", CASE WHEN group_set=1 THEN COUNT(1) END as "flight_count__1", CASE WHEN group_set=1 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__1", (CASE WHEN group_set=1 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=0 THEN COUNT(1) END)) OVER () as "percent_of_flights__1", CASE WHEN group_set IN (2,3) THEN CONCAT(destination_0."code",' - ',destination_0."city") END as "destination_name__2", CASE WHEN group_set=2 THEN COUNT(1) END as "flight_count__2", CASE WHEN group_set=2 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__2", (CASE WHEN group_set=2 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=1 THEN COUNT(1) END)) OVER (PARTITION BY CASE WHEN group_set IN (1,2,3,4,5) THEN CONCAT(origin_0."code",' - ',origin_0."city") END) as "percent_of_flights__2", CASE WHEN group_set=3 THEN carriers_0."nickname" END as "nickname__3", CASE WHEN group_set=3 THEN COUNT(1) END as "flight_count__3", CASE WHEN group_set=4 THEN carriers_0."nickname" END as "carrier_name__4", CASE WHEN group_set=4 THEN COUNT(1) END as "flight_count__4", CASE WHEN group_set=5 THEN DATE_TRUNC('month', base."dep_time") END as "dep_month__5", CASE WHEN group_set=5 THEN COUNT(1) END as "flight_count__5" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/airports.parquet' AS origin_0 ON origin_0."code"=base."origin" LEFT JOIN '../../documentation/data/airports.parquet' AS destination_0 ON destination_0."code"=base."destination" LEFT JOIN '../../documentation/data/carriers.parquet' AS carriers_0 ON carriers_0."code"=base."carrier" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,5,1)) as group_set ) as group_set WHERE origin_0."state"='CA' GROUP BY 1,2,6,10,12,14 ) , __stage1 AS ( SELECT *, CASE WHEN GROUP_SET=2 THEN ROW_NUMBER() OVER (PARTITION BY CAST("origin_name__1" as VARCHAR), group_set ORDER BY "flight_count__2" desc) END as __row_number__2 FROM __stage0 ) , __stage2 AS ( SELECT *, MAX(CASE WHEN group_set IN (2,3) THEN CASE WHEN (GROUP_SET = 2 AND __row_number__2 > 6) THEN 1 ELSE 0 END END) OVER(PARTITION BY CAST("destination_name__2" as VARCHAR),CAST("origin_name__1" as VARCHAR)) as __shaving__2 FROM __stage1 WHERE 1=1 ) , __stage3 AS ( SELECT * FROM __stage2 WHERE NOT ((group_set IN (2,3) AND __shaving__2 > 0) ) ) , __stage4 AS ( SELECT CASE WHEN group_set=3 THEN 2 ELSE group_set END as group_set, CASE WHEN group_set IN (1,2,3,4,5) THEN "origin_name__1" END as "origin_name__1", FIRST("flight_count__1") FILTER (WHERE "flight_count__1" IS NOT NULL) as "flight_count__1", FIRST("total_distance__1") FILTER (WHERE "total_distance__1" IS NOT NULL) as "total_distance__1", FIRST("percent_of_flights__1") FILTER (WHERE "percent_of_flights__1" IS NOT NULL) as "percent_of_flights__1", CASE WHEN group_set IN (2,3) THEN "destination_name__2" END as "destination_name__2", FIRST("flight_count__2") FILTER (WHERE "flight_count__2" IS NOT NULL) as "flight_count__2", FIRST("total_distance__2") FILTER (WHERE "total_distance__2" IS NOT NULL) as "total_distance__2", FIRST("percent_of_flights__2") FILTER (WHERE "percent_of_flights__2" IS NOT NULL) as "percent_of_flights__2", COALESCE(LIST({ "nickname": "nickname__3", "flight_count": "flight_count__3"} ORDER BY "flight_count__3" DESC NULLS LAST) FILTER (WHERE group_set=3),[]) as "cl__2", CASE WHEN group_set=4 THEN "carrier_name__4" END as "carrier_name__4", FIRST("flight_count__4") FILTER (WHERE "flight_count__4" IS NOT NULL) as "flight_count__4", CASE WHEN group_set=5 THEN "dep_month__5" END as "dep_month__5", FIRST("flight_count__5") FILTER (WHERE "flight_count__5" IS NOT NULL) as "flight_count__5" FROM __stage3 WHERE group_set NOT IN (0) GROUP BY 1,2,6,11,13 ) SELECT "origin_name__1" as "origin_name", MAX(CASE WHEN group_set=1 THEN "flight_count__1" END) as "flight_count", MAX(CASE WHEN group_set=1 THEN "total_distance__1" END) as "total_distance", MAX(CASE WHEN group_set=1 THEN "percent_of_flights__1" END) as "percent_of_flights", COALESCE(LIST({ "destination_name": "destination_name__2", "flight_count": "flight_count__2", "total_distance": "total_distance__2", "percent_of_flights": "percent_of_flights__2", "cl": "cl__2"} ORDER BY "flight_count__2" DESC NULLS LAST) FILTER (WHERE group_set=2),[]) as "destination_name", COALESCE(LIST({ "carrier_name": "carrier_name__4", "flight_count": "flight_count__4"} ORDER BY "flight_count__4" DESC NULLS LAST) FILTER (WHERE group_set=4),[]) as "carrier_name", COALESCE(LIST({ "dep_month": "dep_month__5", "flight_count": "flight_count__5"} ORDER BY "dep_month__5" DESC NULLS LAST) FILTER (WHERE group_set=5),[]) as "dep_month" FROM __stage4 GROUP BY 1 ORDER BY 2 desc NULLS LAST
Wait, that looks really complex? What do those things mean?
If you press the control key and hover over any of the terms, the Malloy VSCode extension will show you the semantic definition for the term.
Back to Step 1: How big is the pile?
Ok, let's slow down and go step by step.
Often, the first thing you want to know is, how big is the dataset?
run: flights-> flight_count[ { "flight_count": 344827 } ]
SELECT COUNT(1) as "flight_count" FROM '../../documentation/data/flights.parquet' as base
In Malloy, queries start with run: <source>. In this case, flights. The -> is the query transform operator. The right hand side of the -> is the query transformation. In this case we want a simple measure, flight_count.
Dimensions and Measures
As we talked about earlier. Aggregating queries have two parts: what you want to group by, and what you want to measure about things in that group. Let's group the flights by the origin, and count how many flights. When building a query, we use the + operator to combine the parts.
run: flights -> origin_name + flight_count [ { "origin_name": "ATL - ATLANTA", "flight_count": 17875 }, { "origin_name": "DFW - DALLAS-FORT WORTH", "flight_count": 17782 }, { "origin_name": "ORD - CHICAGO", "flight_count": 14214 }, { "origin_name": "PHX - PHOENIX", "flight_count": 12476 }, { "origin_name": "LAS - LAS VEGAS", "flight_count": 11096 }, { "origin_name": "LAX - LOS ANGELES", "flight_count": 11077 }, { "origin_name": "MSP - MINNEAPOLIS", "flight_count": 9762 }, { "origin_name": "DTW - DETROIT", "flight_count": 8161 }, { "origin_name": "PHL - PHILADELPHIA", "flight_count": 7708 }, { "origin_name": "LGA - NEW YORK", "flight_count": 7623 }, { "origin_name": "DEN - DENVER", "flight_count": 7190 }, { "origin_name": "BWI - BALTIMORE", "flight_count": 7177 }, { "origin_name": "CLT - CHARLOTTE", "flight_count": 7099 }, { "origin_name": "SEA - SEATTLE", "flight_count": 7010 }, { "origin_name": "MCO - ORLANDO", "flight_count": 6790 }, { "origin_name": "DCA - WASHINGTON", "flight_count": 6678 }, { "origin_name": "IAH - HOUSTON", "flight_count": 6623 }, { "origin_name": "MDW - CHICAGO", "flight_count": 6611 }, { "origin_name": "BOS - BOSTON", "flight_count": 5797 }, { "origin_name": "EWR - NEWARK", "flight_count": 5174 }, { "origin_name": "CLE - CLEVELAND", "flight_count": 5127 }, { "origin_name": "OAK - OAKLAND", "flight_count": 5076 }, { "origin_name": "SAN - SAN DIEGO", "flight_count": 5075 }, { "origin_name": "TPA - TAMPA", "flight_count": 4868 }, { "origin_name": "PIT - PITTSBURGH", "flight_count": 4541 }, { "origin_name": "SFO - SAN FRANCISCO", "flight_count": 4540 }, { "origin_name": "BNA - NASHVILLE", "flight_count": 4287 }, { "origin_name": "STL - ST LOUIS", "flight_count": 4089 }, { "origin_name": "MCI - KANSAS CITY", "flight_count": 4078 }, { "origin_name": "IAD - WASHINGTON", "flight_count": 3885 }, { "origin_name": "SJC - SAN JOSE", "flight_count": 3825 }, { "origin_name": "JFK - NEW YORK", "flight_count": 3689 }, { "origin_name": "FLL - FORT LAUDERDALE", "flight_count": 3619 }, { "origin_name": "PDX - PORTLAND", "flight_count": 3596 }, { "origin_name": "SMF - SACRAMENTO", "flight_count": 3576 }, { "origin_name": "HOU - HOUSTON", "flight_count": 3430 }, { "origin_name": "CVG - COVINGTON/CINCINNATI, OH", "flight_count": 3300 }, { "origin_name": "MSY - NEW ORLEANS", "flight_count": 3254 }, { "origin_name": "ABQ - ALBUQUERQUE", "flight_count": 2762 }, { "origin_name": "SLC - SALT LAKE CITY", "flight_count": 2741 }, { "origin_name": "ONT - ONTARIO", "flight_count": 2642 }, { "origin_name": "RDU - RALEIGH/DURHAM", "flight_count": 2444 }, { "origin_name": "MIA - MIAMI", "flight_count": 2387 }, { "origin_name": "PVD - PROVIDENCE", "flight_count": 2361 }, { "origin_name": "MEM - MEMPHIS", "flight_count": 2242 }, { "origin_name": "IND - INDIANAPOLIS", "flight_count": 2226 }, { "origin_name": "BDL - WINDSOR LOCKS", "flight_count": 2168 }, { "origin_name": "AUS - AUSTIN", "flight_count": 2091 }, { "origin_name": "RNO - RENO", "flight_count": 1992 }, { "origin_name": "BUR - BURBANK", "flight_count": 1952 }, { "origin_name": "SNA - SANTA ANA", "flight_count": 1919 }, { "origin_name": "SAT - SAN ANTONIO", "flight_count": 1865 }, { "origin_name": "CMH - COLUMBUS", "flight_count": 1712 }, { "origin_name": "DAL - DALLAS", "flight_count": 1662 }, { "origin_name": "MHT - MANCHESTER", "flight_count": 1626 }, { "origin_name": "JAX - JACKSONVILLE", "flight_count": 1599 }, { "origin_name": "ELP - EL PASO", "flight_count": 1438 }, { "origin_name": "PBI - WEST PALM BEACH", "flight_count": 1379 }, { "origin_name": "ISP - ISLIP", "flight_count": 1302 }, { "origin_name": "BUF - BUFFALO", "flight_count": 1246 }, { "origin_name": "BHM - BIRMINGHAM", "flight_count": 1200 }, { "origin_name": "RSW - FORT MYERS", "flight_count": 1169 }, { "origin_name": "MKE - MILWAUKEE", "flight_count": 1132 }, { "origin_name": "ALB - ALBANY", "flight_count": 1108 }, { "origin_name": "TUS - TUCSON", "flight_count": 1062 }, { "origin_name": "SDF - LOUISVILLE", "flight_count": 1003 }, { "origin_name": "TUL - TULSA", "flight_count": 984 }, { "origin_name": "OMA - OMAHA", "flight_count": 946 }, { "origin_name": "RIC - RICHMOND", "flight_count": 946 }, { "origin_name": "GEG - SPOKANE", "flight_count": 922 }, { "origin_name": "OKC - OKLAHOMA CITY", "flight_count": 905 }, { "origin_name": "ORF - NORFOLK", "flight_count": 888 }, { "origin_name": "LIT - LITTLE ROCK", "flight_count": 853 }, { "origin_name": "BOI - BOISE", "flight_count": 795 }, { "origin_name": "PFN - PANAMA CITY", "flight_count": 778 }, { "origin_name": "AGS - AUGUSTA", "flight_count": 756 }, { "origin_name": "ANC - ANCHORAGE", "flight_count": 748 }, { "origin_name": "ROC - ROCHESTER", "flight_count": 733 }, { "origin_name": "AVL - ASHEVILLE", "flight_count": 727 }, { "origin_name": "CHA - CHATTANOOGA", "flight_count": 697 }, { "origin_name": "LGB - LONG BEACH", "flight_count": 661 }, { "origin_name": "CSG - COLUMBUS", "flight_count": 654 }, { "origin_name": "SJU - SAN JUAN", "flight_count": 645 }, { "origin_name": "MYR - MYRTLE BEACH", "flight_count": 628 }, { "origin_name": "HSV - HUNTSVILLE", "flight_count": 607 }, { "origin_name": "SYR - SYRACUSE", "flight_count": 597 }, { "origin_name": "MDT - HARRISBURG", "flight_count": 584 }, { "origin_name": "TRI - BRISTOL/JOHNSON/KINGSPORT", "flight_count": 566 }, { "origin_name": "GRR - GRAND RAPIDS", "flight_count": 513 }, { "origin_name": "ABE - ALLENTOWN", "flight_count": 510 }, { "origin_name": "MGM - MONTGOMERY", "flight_count": 500 }, { "origin_name": "ACT - WACO", "flight_count": 494 }, { "origin_name": "TYR - TYLER", "flight_count": 480 }, { "origin_name": "GSO - GREENSBORO", "flight_count": 470 }, { "origin_name": "GNV - GAINESVILLE", "flight_count": 470 }, { "origin_name": "CHS - CHARLESTON", "flight_count": 466 }, { "origin_name": "TYS - KNOXVILLE", "flight_count": 464 }, { "origin_name": "SPS - WICHITA FALLS", "flight_count": 457 }, { "origin_name": "CLL - COLLEGE STATION", "flight_count": 454 }, { "origin_name": "COS - COLORADO SPRINGS", "flight_count": 426 }, { "origin_name": "LBB - LUBBOCK", "flight_count": 425 }, { "origin_name": "LAW - LAWTON", "flight_count": 423 }, { "origin_name": "SAV - SAVANNAH", "flight_count": 419 }, { "origin_name": "ILE - KILLEEN", "flight_count": 416 }, { "origin_name": "DAY - DAYTON", "flight_count": 416 }, { "origin_name": "MAF - MIDLAND", "flight_count": 409 }, { "origin_name": "SHV - SHREVEPORT", "flight_count": 406 }, { "origin_name": "VPS - VALPARAISO", "flight_count": 403 }, { "origin_name": "CAE - COLUMBIA", "flight_count": 376 }, { "origin_name": "JAN - JACKSON", "flight_count": 356 }, { "origin_name": "PWM - PORTLAND", "flight_count": 326 }, { "origin_name": "DHN - DOTHAN", "flight_count": 324 }, { "origin_name": "XNA - FAYETTEVILLE/SPRINGDALE/", "flight_count": 319 }, { "origin_name": "HPN - WHITE PLAINS", "flight_count": 315 }, { "origin_name": "BTV - BURLINGTON", "flight_count": 307 }, { "origin_name": "LEX - LEXINGTON", "flight_count": 305 }, { "origin_name": "AMA - AMARILLO", "flight_count": 298 }, { "origin_name": "ABI - ABILENE", "flight_count": 297 }, { "origin_name": "TLH - TALLAHASSEE", "flight_count": 285 }, { "origin_name": "GPT - GULFPORT", "flight_count": 278 }, { "origin_name": "BTR - BATON ROUGE", "flight_count": 274 }, { "origin_name": "MSN - MADISON", "flight_count": 271 }, { "origin_name": "HNL - HONOLULU", "flight_count": 267 }, { "origin_name": "GGG - LONGVIEW", "flight_count": 258 }, { "origin_name": "DSM - DES MOINES", "flight_count": 254 }, { "origin_name": "GSP - GREER", "flight_count": 241 }, { "origin_name": "PSP - PALM SPRINGS", "flight_count": 240 }, { "origin_name": "SJT - SAN ANGELO", "flight_count": 223 }, { "origin_name": "TXK - TEXARKANA", "flight_count": 207 }, { "origin_name": "FSM - FORT SMITH", "flight_count": 202 }, { "origin_name": "VLD - VALDOSTA", "flight_count": 200 }, { "origin_name": "PNS - PENSACOLA", "flight_count": 194 }, { "origin_name": "SRQ - SARASOTA/BRADENTON", "flight_count": 192 }, { "origin_name": "SGF - SPRINGFIELD", "flight_count": 185 }, { "origin_name": "HRL - HARLINGEN", "flight_count": 185 }, { "origin_name": "ICT - WICHITA", "flight_count": 183 }, { "origin_name": "CRP - CORPUS CHRISTI", "flight_count": 181 }, { "origin_name": "LFT - LAFAYETTE", "flight_count": 176 }, { "origin_name": "GRK - FORT HOOD/KILLEEN", "flight_count": 161 }, { "origin_name": "ROA - ROANOKE", "flight_count": 158 }, { "origin_name": "MFE - MC ALLEN", "flight_count": 154 }, { "origin_name": "ILM - WILMINGTON", "flight_count": 128 }, { "origin_name": "MOB - MOBILE", "flight_count": 125 }, { "origin_name": "FAI - FAIRBANKS", "flight_count": 114 }, { "origin_name": "OGG - KAHULUI", "flight_count": 110 }, { "origin_name": "LRD - LAREDO", "flight_count": 107 }, { "origin_name": "MCN - MACON", "flight_count": 102 }, { "origin_name": "BGR - BANGOR", "flight_count": 94 }, { "origin_name": "BRO - BROWNSVILLE", "flight_count": 94 }, { "origin_name": "JAC - JACKSON", "flight_count": 86 }, { "origin_name": "FAR - FARGO", "flight_count": 83 }, { "origin_name": "FNT - FLINT", "flight_count": 83 }, { "origin_name": "FAY - FAYETTEVILLE", "flight_count": 83 }, { "origin_name": "AEX - ALEXANDRIA", "flight_count": 80 }, { "origin_name": "BPT - BEAUMONT/PORT ARTHUR", "flight_count": 77 }, { "origin_name": "STT - CHARLOTTE AMALIE", "flight_count": 76 }, { "origin_name": "PIE - ST PETERSBURG-CLEARWATER", "flight_count": 65 }, { "origin_name": "BZN - BOZEMAN", "flight_count": 62 }, { "origin_name": "FAT - FRESNO", "flight_count": 57 }, { "origin_name": "AVP - WILKES-BARRE/SCRANTON", "flight_count": 51 }, { "origin_name": "GRB - GREEN BAY", "flight_count": 47 }, { "origin_name": "EVV - EVANSVILLE", "flight_count": 42 }, { "origin_name": "MSO - MISSOULA", "flight_count": 41 }, { "origin_name": "LCH - LAKE CHARLES", "flight_count": 41 }, { "origin_name": "CID - CEDAR RAPIDS", "flight_count": 40 }, { "origin_name": "TOL - TOLEDO", "flight_count": 40 }, { "origin_name": "DLH - DULUTH", "flight_count": 40 }, { "origin_name": "LAN - LANSING", "flight_count": 40 }, { "origin_name": "GTR - COLUMBUS/W POINT/STARKVILL", "flight_count": 37 }, { "origin_name": "MEI - MERIDIAN", "flight_count": 37 }, { "origin_name": "FSD - SIOUX FALLS", "flight_count": 35 }, { "origin_name": "CRW - CHARLESTON", "flight_count": 32 }, { "origin_name": "MTJ - MONTROSE", "flight_count": 32 }, { "origin_name": "CAK - AKRON", "flight_count": 32 }, { "origin_name": "DAB - DAYTONA BEACH", "flight_count": 31 }, { "origin_name": "FWA - FORT WAYNE", "flight_count": 31 }, { "origin_name": "SBN - SOUTH BEND", "flight_count": 25 }, { "origin_name": "EGE - EAGLE", "flight_count": 24 }, { "origin_name": "ATW - APPLETON", "flight_count": 24 }, { "origin_name": "KOA - KAILUA/KONA", "flight_count": 24 }, { "origin_name": "RAP - RAPID CITY", "flight_count": 22 }, { "origin_name": "EFD - HOUSTON", "flight_count": 21 }, { "origin_name": "ERI - ERIE", "flight_count": 21 }, { "origin_name": "AZO - KALAMAZOO", "flight_count": 19 }, { "origin_name": "SBA - SANTA BARBARA", "flight_count": 18 }, { "origin_name": "ACY - ATLANTIC CITY", "flight_count": 18 }, { "origin_name": "GTF - GREAT FALLS", "flight_count": 18 }, { "origin_name": "FCA - KALISPELL", "flight_count": 17 }, { "origin_name": "MLI - MOLINE", "flight_count": 17 }, { "origin_name": "BIL - BILLINGS", "flight_count": 15 }, { "origin_name": "MLU - MONROE", "flight_count": 15 }, { "origin_name": "ACK - NANTUCKET", "flight_count": 15 }, { "origin_name": " - ", "flight_count": 14 }, { "origin_name": "LYH - LYNCHBURG", "flight_count": 14 }, { "origin_name": "SWF - NEWBURGH", "flight_count": 13 }, { "origin_name": "BGM - BINGHAMTON", "flight_count": 13 }, { "origin_name": "ORH - WORCESTER", "flight_count": 12 }, { "origin_name": "HDN - HAYDEN", "flight_count": 12 }, { "origin_name": "MLB - MELBOURNE", "flight_count": 10 }, { "origin_name": "BQN - AGUADILLA", "flight_count": 9 }, { "origin_name": "BFL - BAKERSFIELD", "flight_count": 9 }, { "origin_name": "CHO - CHARLOTTESVILLE", "flight_count": 9 }, { "origin_name": "LNK - LINCOLN", "flight_count": 9 }, { "origin_name": "CMI - CHAMPAIGN/URBANA", "flight_count": 8 }, { "origin_name": "GUC - GUNNISON", "flight_count": 7 }, { "origin_name": "ABY - ALBANY", "flight_count": 6 }, { "origin_name": "HLN - HELENA", "flight_count": 5 }, { "origin_name": "EUG - EUGENE", "flight_count": 5 }, { "origin_name": "BIS - BISMARCK", "flight_count": 5 }, { "origin_name": "DRO - DURANGO", "flight_count": 5 }, { "origin_name": "HVN - NEW HAVEN", "flight_count": 4 }, { "origin_name": "HTS - HUNTINGTON", "flight_count": 4 }, { "origin_name": "PSE - PONCE", "flight_count": 3 }, { "origin_name": "STX - CHRISTIANSTED", "flight_count": 3 }, { "origin_name": "VCT - VICTORIA", "flight_count": 3 }, { "origin_name": "EYW - KEY WEST", "flight_count": 3 }, { "origin_name": "MRY - MONTEREY", "flight_count": 3 }, { "origin_name": "TVC - TRAVERSE CITY", "flight_count": 2 }, { "origin_name": "BQK - BRUNSWICK", "flight_count": 2 }, { "origin_name": "LSE - LA CROSSE", "flight_count": 1 }, { "origin_name": "PHF - NEWPORT NEWS", "flight_count": 1 }, { "origin_name": "FLO - FLORENCE", "flight_count": 1 }, { "origin_name": "MFR - MEDFORD", "flight_count": 1 }, { "origin_name": "ITH - ITHACA", "flight_count": 1 }, { "origin_name": "ELM - ELMIRA/CORNING", "flight_count": 1 } ]
SELECT CONCAT(origin_0."code",' - ',origin_0."city") as "origin_name", COUNT(1) as "flight_count" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/airports.parquet' AS origin_0 ON origin_0."code"=base."origin" GROUP BY 1 ORDER BY 2 desc NULLS LAST
Seats in the Air
Another way of measuring the busyness of an airport is to try and estimate the number of people that travel through the airport. Planes are of different sizes. If we count up all the seats in all the planes that have arrived, we can approximate the busyness. The measure seats_for_sale will give us the maximum number of people that could have landed there.
Notice Chicago has more people traveling through than Dallas-Fort Worth.
run: flights -> origin_name + seats_for_sale + flight_count[ { "origin_name": "ATL - ATLANTA", "seats_for_sale": "2686015", "flight_count": 17875 }, { "origin_name": "ORD - CHICAGO", "seats_for_sale": "2430878", "flight_count": 14214 }, { "origin_name": "DFW - DALLAS-FORT WORTH", "seats_for_sale": "2123314", "flight_count": 17782 }, { "origin_name": "LAX - LOS ANGELES", "seats_for_sale": "1936876", "flight_count": 11077 }, { "origin_name": "PHX - PHOENIX", "seats_for_sale": "1919936", "flight_count": 12476 }, { "origin_name": "LAS - LAS VEGAS", "seats_for_sale": "1814378", "flight_count": 11096 }, { "origin_name": "MSP - MINNEAPOLIS", "seats_for_sale": "1714313", "flight_count": 9762 }, { "origin_name": "DEN - DENVER", "seats_for_sale": "1462336", "flight_count": 7190 }, { "origin_name": "DTW - DETROIT", "seats_for_sale": "1341497", "flight_count": 8161 }, { "origin_name": "SEA - SEATTLE", "seats_for_sale": "1245108", "flight_count": 7010 }, { "origin_name": "LGA - NEW YORK", "seats_for_sale": "1227369", "flight_count": 7623 }, { "origin_name": "MCO - ORLANDO", "seats_for_sale": "1148702", "flight_count": 6790 }, { "origin_name": "PHL - PHILADELPHIA", "seats_for_sale": "1142035", "flight_count": 7708 }, { "origin_name": "CLT - CHARLOTTE", "seats_for_sale": "1115730", "flight_count": 7099 }, { "origin_name": "BWI - BALTIMORE", "seats_for_sale": "1064319", "flight_count": 7177 }, { "origin_name": "DCA - WASHINGTON", "seats_for_sale": "975309", "flight_count": 6678 }, { "origin_name": "MDW - CHICAGO", "seats_for_sale": "958896", "flight_count": 6611 }, { "origin_name": "BOS - BOSTON", "seats_for_sale": "917748", "flight_count": 5797 }, { "origin_name": "SFO - SAN FRANCISCO", "seats_for_sale": "848509", "flight_count": 4540 }, { "origin_name": "TPA - TAMPA", "seats_for_sale": "825925", "flight_count": 4868 }, { "origin_name": "SAN - SAN DIEGO", "seats_for_sale": "823100", "flight_count": 5075 }, { "origin_name": "IAD - WASHINGTON", "seats_for_sale": "801083", "flight_count": 3885 }, { "origin_name": "OAK - OAKLAND", "seats_for_sale": "775116", "flight_count": 5076 }, { "origin_name": "IAH - HOUSTON", "seats_for_sale": "703954", "flight_count": 6623 }, { "origin_name": "PIT - PITTSBURGH", "seats_for_sale": "676974", "flight_count": 4541 }, { "origin_name": "JFK - NEW YORK", "seats_for_sale": "626555", "flight_count": 3689 }, { "origin_name": "FLL - FORT LAUDERDALE", "seats_for_sale": "619704", "flight_count": 3619 }, { "origin_name": "PDX - PORTLAND", "seats_for_sale": "608348", "flight_count": 3596 }, { "origin_name": "SJC - SAN JOSE", "seats_for_sale": "601204", "flight_count": 3825 }, { "origin_name": "STL - ST LOUIS", "seats_for_sale": "596188", "flight_count": 4089 }, { "origin_name": "EWR - NEWARK", "seats_for_sale": "594868", "flight_count": 5174 }, { "origin_name": "BNA - NASHVILLE", "seats_for_sale": "594525", "flight_count": 4287 }, { "origin_name": "MCI - KANSAS CITY", "seats_for_sale": "583287", "flight_count": 4078 }, { "origin_name": "SLC - SALT LAKE CITY", "seats_for_sale": "548278", "flight_count": 2741 }, { "origin_name": "SMF - SACRAMENTO", "seats_for_sale": "546267", "flight_count": 3576 }, { "origin_name": "MSY - NEW ORLEANS", "seats_for_sale": "519599", "flight_count": 3254 }, { "origin_name": "HOU - HOUSTON", "seats_for_sale": "494499", "flight_count": 3430 }, { "origin_name": "MIA - MIAMI", "seats_for_sale": "439047", "flight_count": 2387 }, { "origin_name": "CLE - CLEVELAND", "seats_for_sale": "417097", "flight_count": 5127 }, { "origin_name": "ONT - ONTARIO", "seats_for_sale": "411496", "flight_count": 2642 }, { "origin_name": "ABQ - ALBUQUERQUE", "seats_for_sale": "411311", "flight_count": 2762 }, { "origin_name": "CVG - COVINGTON/CINCINNATI, OH", "seats_for_sale": "388118", "flight_count": 3300 }, { "origin_name": "RDU - RALEIGH/DURHAM", "seats_for_sale": "355047", "flight_count": 2444 }, { "origin_name": "MEM - MEMPHIS", "seats_for_sale": "342259", "flight_count": 2242 }, { "origin_name": "PVD - PROVIDENCE", "seats_for_sale": "341742", "flight_count": 2361 }, { "origin_name": "BDL - WINDSOR LOCKS", "seats_for_sale": "336565", "flight_count": 2168 }, { "origin_name": "AUS - AUSTIN", "seats_for_sale": "321070", "flight_count": 2091 }, { "origin_name": "RNO - RENO", "seats_for_sale": "307684", "flight_count": 1992 }, { "origin_name": "IND - INDIANAPOLIS", "seats_for_sale": "307466", "flight_count": 2226 }, { "origin_name": "SNA - SANTA ANA", "seats_for_sale": "302956", "flight_count": 1919 }, { "origin_name": "BUR - BURBANK", "seats_for_sale": "295747", "flight_count": 1952 }, { "origin_name": "SAT - SAN ANTONIO", "seats_for_sale": "279791", "flight_count": 1865 }, { "origin_name": "JAX - JACKSONVILLE", "seats_for_sale": "276128", "flight_count": 1599 }, { "origin_name": "PBI - WEST PALM BEACH", "seats_for_sale": "248750", "flight_count": 1379 }, { "origin_name": "MHT - MANCHESTER", "seats_for_sale": "239093", "flight_count": 1626 }, { "origin_name": "CMH - COLUMBUS", "seats_for_sale": "238801", "flight_count": 1712 }, { "origin_name": "DAL - DALLAS", "seats_for_sale": "238567", "flight_count": 1662 }, { "origin_name": "RSW - FORT MYERS", "seats_for_sale": "226806", "flight_count": 1169 }, { "origin_name": "ELP - EL PASO", "seats_for_sale": "214052", "flight_count": 1438 }, { "origin_name": "ISP - ISLIP", "seats_for_sale": "171446", "flight_count": 1302 }, { "origin_name": "BUF - BUFFALO", "seats_for_sale": "169796", "flight_count": 1246 }, { "origin_name": "TUS - TUCSON", "seats_for_sale": "166162", "flight_count": 1062 }, { "origin_name": "BHM - BIRMINGHAM", "seats_for_sale": "159562", "flight_count": 1200 }, { "origin_name": "ANC - ANCHORAGE", "seats_for_sale": "158055", "flight_count": 748 }, { "origin_name": "MKE - MILWAUKEE", "seats_for_sale": "152217", "flight_count": 1132 }, { "origin_name": "GEG - SPOKANE", "seats_for_sale": "144575", "flight_count": 922 }, { "origin_name": "RIC - RICHMOND", "seats_for_sale": "138549", "flight_count": 946 }, { "origin_name": "ALB - ALBANY", "seats_for_sale": "133982", "flight_count": 1108 }, { "origin_name": "OMA - OMAHA", "seats_for_sale": "131609", "flight_count": 946 }, { "origin_name": "SJU - SAN JUAN", "seats_for_sale": "129403", "flight_count": 645 }, { "origin_name": "LGB - LONG BEACH", "seats_for_sale": "125638", "flight_count": 661 }, { "origin_name": "SDF - LOUISVILLE", "seats_for_sale": "123124", "flight_count": 1003 }, { "origin_name": "TUL - TULSA", "seats_for_sale": "121753", "flight_count": 984 }, { "origin_name": "BOI - BOISE", "seats_for_sale": "117813", "flight_count": 795 }, { "origin_name": "ORF - NORFOLK", "seats_for_sale": "113904", "flight_count": 888 }, { "origin_name": "OKC - OKLAHOMA CITY", "seats_for_sale": "104570", "flight_count": 905 }, { "origin_name": "ROC - ROCHESTER", "seats_for_sale": "97846", "flight_count": 733 }, { "origin_name": "LIT - LITTLE ROCK", "seats_for_sale": "83404", "flight_count": 853 }, { "origin_name": "HSV - HUNTSVILLE", "seats_for_sale": "74655", "flight_count": 607 }, { "origin_name": "SYR - SYRACUSE", "seats_for_sale": "70910", "flight_count": 597 }, { "origin_name": "MYR - MYRTLE BEACH", "seats_for_sale": "69135", "flight_count": 628 }, { "origin_name": "MDT - HARRISBURG", "seats_for_sale": "66779", "flight_count": 584 }, { "origin_name": "COS - COLORADO SPRINGS", "seats_for_sale": "61809", "flight_count": 426 }, { "origin_name": "PFN - PANAMA CITY", "seats_for_sale": "59086", "flight_count": 778 }, { "origin_name": "CHS - CHARLESTON", "seats_for_sale": "57840", "flight_count": 466 }, { "origin_name": "GRR - GRAND RAPIDS", "seats_for_sale": "57828", "flight_count": 513 }, { "origin_name": "AVL - ASHEVILLE", "seats_for_sale": "57624", "flight_count": 727 }, { "origin_name": "AGS - AUGUSTA", "seats_for_sale": "56508", "flight_count": 756 }, { "origin_name": "SAV - SAVANNAH", "seats_for_sale": "56323", "flight_count": 419 }, { "origin_name": "ABE - ALLENTOWN", "seats_for_sale": "54345", "flight_count": 510 }, { "origin_name": "GSO - GREENSBORO", "seats_for_sale": "53645", "flight_count": 470 }, { "origin_name": "CHA - CHATTANOOGA", "seats_for_sale": "52611", "flight_count": 697 }, { "origin_name": "CSG - COLUMBUS", "seats_for_sale": "49484", "flight_count": 654 }, { "origin_name": "JAN - JACKSON", "seats_for_sale": "45800", "flight_count": 356 }, { "origin_name": "CAE - COLUMBIA", "seats_for_sale": "44762", "flight_count": 376 }, { "origin_name": "LBB - LUBBOCK", "seats_for_sale": "43451", "flight_count": 425 }, { "origin_name": "TRI - BRISTOL/JOHNSON/KINGSPORT", "seats_for_sale": "42761", "flight_count": 566 }, { "origin_name": "MAF - MIDLAND", "seats_for_sale": "41588", "flight_count": 409 }, { "origin_name": "TYS - KNOXVILLE", "seats_for_sale": "39145", "flight_count": 464 }, { "origin_name": "PSP - PALM SPRINGS", "seats_for_sale": "39058", "flight_count": 240 }, { "origin_name": "HNL - HONOLULU", "seats_for_sale": "38498", "flight_count": 267 }, { "origin_name": "TLH - TALLAHASSEE", "seats_for_sale": "38374", "flight_count": 285 }, { "origin_name": "MGM - MONTGOMERY", "seats_for_sale": "38000", "flight_count": 500 }, { "origin_name": "GNV - GAINESVILLE", "seats_for_sale": "35720", "flight_count": 470 }, { "origin_name": "BTV - BURLINGTON", "seats_for_sale": "35047", "flight_count": 307 }, { "origin_name": "DAY - DAYTON", "seats_for_sale": "30449", "flight_count": 416 }, { "origin_name": "VPS - VALPARAISO", "seats_for_sale": "30250", "flight_count": 403 }, { "origin_name": "MSN - MADISON", "seats_for_sale": "29127", "flight_count": 271 }, { "origin_name": "AMA - AMARILLO", "seats_for_sale": "28588", "flight_count": 298 }, { "origin_name": "SRQ - SARASOTA/BRADENTON", "seats_for_sale": "26930", "flight_count": 192 }, { "origin_name": "PWM - PORTLAND", "seats_for_sale": "26890", "flight_count": 326 }, { "origin_name": "DHN - DOTHAN", "seats_for_sale": "24580", "flight_count": 324 }, { "origin_name": "MFE - MC ALLEN", "seats_for_sale": "22795", "flight_count": 154 }, { "origin_name": "BTR - BATON ROUGE", "seats_for_sale": "21359", "flight_count": 274 }, { "origin_name": "HRL - HARLINGEN", "seats_for_sale": "20877", "flight_count": 185 }, { "origin_name": "GPT - GULFPORT", "seats_for_sale": "20478", "flight_count": 278 }, { "origin_name": "LEX - LEXINGTON", "seats_for_sale": "20175", "flight_count": 305 }, { "origin_name": "FAI - FAIRBANKS", "seats_for_sale": "19668", "flight_count": 114 }, { "origin_name": "PNS - PENSACOLA", "seats_for_sale": "19510", "flight_count": 194 }, { "origin_name": "GSP - GREER", "seats_for_sale": "18898", "flight_count": 241 }, { "origin_name": "ACT - WACO", "seats_for_sale": "18734", "flight_count": 494 }, { "origin_name": "ILM - WILMINGTON", "seats_for_sale": "18373", "flight_count": 128 }, { "origin_name": "TYR - TYLER", "seats_for_sale": "18196", "flight_count": 480 }, { "origin_name": "CLL - COLLEGE STATION", "seats_for_sale": "17256", "flight_count": 454 }, { "origin_name": "SPS - WICHITA FALLS", "seats_for_sale": "17243", "flight_count": 457 }, { "origin_name": "SHV - SHREVEPORT", "seats_for_sale": "16253", "flight_count": 406 }, { "origin_name": "LAW - LAWTON", "seats_for_sale": "16031", "flight_count": 423 }, { "origin_name": "ILE - KILLEEN", "seats_for_sale": "15729", "flight_count": 416 }, { "origin_name": "JAC - JACKSON", "seats_for_sale": "15063", "flight_count": 86 }, { "origin_name": "VLD - VALDOSTA", "seats_for_sale": "14980", "flight_count": 200 }, { "origin_name": "STT - CHARLOTTE AMALIE", "seats_for_sale": "14798", "flight_count": 76 }, { "origin_name": "DSM - DES MOINES", "seats_for_sale": "13818", "flight_count": 254 }, { "origin_name": "ROA - ROANOKE", "seats_for_sale": "13684", "flight_count": 158 }, { "origin_name": "XNA - FAYETTEVILLE/SPRINGDALE/", "seats_for_sale": "13437", "flight_count": 319 }, { "origin_name": "OGG - KAHULUI", "seats_for_sale": "13086", "flight_count": 110 }, { "origin_name": "MOB - MOBILE", "seats_for_sale": "13022", "flight_count": 125 }, { "origin_name": "FAR - FARGO", "seats_for_sale": "12869", "flight_count": 83 }, { "origin_name": "CRP - CORPUS CHRISTI", "seats_for_sale": "12867", "flight_count": 181 }, { "origin_name": "ICT - WICHITA", "seats_for_sale": "12485", "flight_count": 183 }, { "origin_name": "ABI - ABILENE", "seats_for_sale": "11345", "flight_count": 297 }, { "origin_name": "HPN - WHITE PLAINS", "seats_for_sale": "11257", "flight_count": 315 }, { "origin_name": "BZN - BOZEMAN", "seats_for_sale": "10015", "flight_count": 62 }, { "origin_name": "FAT - FRESNO", "seats_for_sale": "9804", "flight_count": 57 }, { "origin_name": "GGG - LONGVIEW", "seats_for_sale": "9790", "flight_count": 258 }, { "origin_name": "PIE - ST PETERSBURG-CLEARWATER", "seats_for_sale": "9685", "flight_count": 65 }, { "origin_name": "FNT - FLINT", "seats_for_sale": "9005", "flight_count": 83 }, { "origin_name": "SJT - SAN ANGELO", "seats_for_sale": "8553", "flight_count": 223 }, { "origin_name": "TXK - TEXARKANA", "seats_for_sale": "7803", "flight_count": 207 }, { "origin_name": "LFT - LAFAYETTE", "seats_for_sale": "7788", "flight_count": 176 }, { "origin_name": "FSM - FORT SMITH", "seats_for_sale": "7764", "flight_count": 202 }, { "origin_name": "MCN - MACON", "seats_for_sale": "7532", "flight_count": 102 }, { "origin_name": "SGF - SPRINGFIELD", "seats_for_sale": "7363", "flight_count": 185 }, { "origin_name": "DLH - DULUTH", "seats_for_sale": "6651", "flight_count": 40 }, { "origin_name": "FAY - FAYETTEVILLE", "seats_for_sale": "6264", "flight_count": 83 }, { "origin_name": "GRK - FORT HOOD/KILLEEN", "seats_for_sale": "6101", "flight_count": 161 }, { "origin_name": "KOA - KAILUA/KONA", "seats_for_sale": "6054", "flight_count": 24 }, { "origin_name": "MSO - MISSOULA", "seats_for_sale": "6045", "flight_count": 41 }, { "origin_name": "AVP - WILKES-BARRE/SCRANTON", "seats_for_sale": "5719", "flight_count": 51 }, { "origin_name": "GRB - GREEN BAY", "seats_for_sale": "5306", "flight_count": 47 }, { "origin_name": "BRO - BROWNSVILLE", "seats_for_sale": "5264", "flight_count": 94 }, { "origin_name": "FSD - SIOUX FALLS", "seats_for_sale": "4746", "flight_count": 35 }, { "origin_name": "LRD - LAREDO", "seats_for_sale": "4479", "flight_count": 107 }, { "origin_name": "EGE - EAGLE", "seats_for_sale": "4272", "flight_count": 24 }, { "origin_name": "BGR - BANGOR", "seats_for_sale": "3712", "flight_count": 94 }, { "origin_name": "MTJ - MONTROSE", "seats_for_sale": "3704", "flight_count": 32 }, { "origin_name": "LAN - LANSING", "seats_for_sale": "3622", "flight_count": 40 }, { "origin_name": "BPT - BEAUMONT/PORT ARTHUR", "seats_for_sale": "3519", "flight_count": 77 }, { "origin_name": "AEX - ALEXANDRIA", "seats_for_sale": "3439", "flight_count": 80 }, { "origin_name": "RAP - RAPID CITY", "seats_for_sale": "3194", "flight_count": 22 }, { "origin_name": "TOL - TOLEDO", "seats_for_sale": "2629", "flight_count": 40 }, { "origin_name": "GTF - GREAT FALLS", "seats_for_sale": "2610", "flight_count": 18 }, { "origin_name": "EVV - EVANSVILLE", "seats_for_sale": "2581", "flight_count": 42 }, { "origin_name": "FCA - KALISPELL", "seats_for_sale": "2495", "flight_count": 17 }, { "origin_name": "BIL - BILLINGS", "seats_for_sale": "2317", "flight_count": 15 }, { "origin_name": "LCH - LAKE CHARLES", "seats_for_sale": "2237", "flight_count": 41 }, { "origin_name": "DAB - DAYTONA BEACH", "seats_for_sale": "2204", "flight_count": 31 }, { "origin_name": "ERI - ERIE", "seats_for_sale": "2189", "flight_count": 21 }, { "origin_name": "CAK - AKRON", "seats_for_sale": "2095", "flight_count": 32 }, { "origin_name": "BQN - AGUADILLA", "seats_for_sale": "1800", "flight_count": 9 }, { "origin_name": "LNK - LINCOLN", "seats_for_sale": "1737", "flight_count": 9 }, { "origin_name": "FWA - FORT WAYNE", "seats_for_sale": "1705", "flight_count": 31 }, { "origin_name": "CRW - CHARLESTON", "seats_for_sale": "1608", "flight_count": 32 }, { "origin_name": "HDN - HAYDEN", "seats_for_sale": "1470", "flight_count": 12 }, { "origin_name": "SBN - SOUTH BEND", "seats_for_sale": "1415", "flight_count": 25 }, { "origin_name": "CID - CEDAR RAPIDS", "seats_for_sale": "1408", "flight_count": 40 }, { "origin_name": "ATW - APPLETON", "seats_for_sale": "1320", "flight_count": 24 }, { "origin_name": "GTR - COLUMBUS/W POINT/STARKVILL", "seats_for_sale": "1228", "flight_count": 37 }, { "origin_name": "MEI - MERIDIAN", "seats_for_sale": "1184", "flight_count": 37 }, { "origin_name": "GUC - GUNNISON", "seats_for_sale": "1171", "flight_count": 7 }, { "origin_name": "EFD - HOUSTON", "seats_for_sale": "1137", "flight_count": 21 }, { "origin_name": "AZO - KALAMAZOO", "seats_for_sale": "1045", "flight_count": 19 }, { "origin_name": "ACY - ATLANTIC CITY", "seats_for_sale": "990", "flight_count": 18 }, { "origin_name": "EUG - EUGENE", "seats_for_sale": "979", "flight_count": 5 }, { "origin_name": "MLI - MOLINE", "seats_for_sale": "935", "flight_count": 17 }, { "origin_name": "BGM - BINGHAMTON", "seats_for_sale": "903", "flight_count": 13 }, { "origin_name": " - ", "seats_for_sale": "770", "flight_count": 14 }, { "origin_name": "DRO - DURANGO", "seats_for_sale": "743", "flight_count": 5 }, { "origin_name": "BIS - BISMARCK", "seats_for_sale": "725", "flight_count": 5 }, { "origin_name": "SWF - NEWBURGH", "seats_for_sale": "715", "flight_count": 13 }, { "origin_name": "ACK - NANTUCKET", "seats_for_sale": "699", "flight_count": 15 }, { "origin_name": "PSE - PONCE", "seats_for_sale": "600", "flight_count": 3 }, { "origin_name": "MLU - MONROE", "seats_for_sale": "577", "flight_count": 15 }, { "origin_name": "MLB - MELBOURNE", "seats_for_sale": "550", "flight_count": 10 }, { "origin_name": "STX - CHRISTIANSTED", "seats_for_sale": "537", "flight_count": 3 }, { "origin_name": "MRY - MONTEREY", "seats_for_sale": "535", "flight_count": 3 }, { "origin_name": "BFL - BAKERSFIELD", "seats_for_sale": "495", "flight_count": 9 }, { "origin_name": "CHO - CHARLOTTESVILLE", "seats_for_sale": "495", "flight_count": 9 }, { "origin_name": "LYH - LYNCHBURG", "seats_for_sale": "492", "flight_count": 14 }, { "origin_name": "ORH - WORCESTER", "seats_for_sale": "444", "flight_count": 12 }, { "origin_name": "CMI - CHAMPAIGN/URBANA", "seats_for_sale": "440", "flight_count": 8 }, { "origin_name": "SBA - SANTA BARBARA", "seats_for_sale": "429", "flight_count": 18 }, { "origin_name": "ABY - ALBANY", "seats_for_sale": "391", "flight_count": 6 }, { "origin_name": "HLN - HELENA", "seats_for_sale": "275", "flight_count": 5 }, { "origin_name": "HVN - NEW HAVEN", "seats_for_sale": "220", "flight_count": 4 }, { "origin_name": "EYW - KEY WEST", "seats_for_sale": "210", "flight_count": 3 }, { "origin_name": "HTS - HUNTINGTON", "seats_for_sale": "197", "flight_count": 4 }, { "origin_name": "TVC - TRAVERSE CITY", "seats_for_sale": "189", "flight_count": 2 }, { "origin_name": "MFR - MEDFORD", "seats_for_sale": "179", "flight_count": 1 }, { "origin_name": "BQK - BRUNSWICK", "seats_for_sale": "152", "flight_count": 2 }, { "origin_name": "ELM - ELMIRA/CORNING", "seats_for_sale": "149", "flight_count": 1 }, { "origin_name": "ITH - ITHACA", "seats_for_sale": "149", "flight_count": 1 }, { "origin_name": "VCT - VICTORIA", "seats_for_sale": "147", "flight_count": 3 }, { "origin_name": "FLO - FLORENCE", "seats_for_sale": "76", "flight_count": 1 }, { "origin_name": "PHF - NEWPORT NEWS", "seats_for_sale": "55", "flight_count": 1 }, { "origin_name": "LSE - LA CROSSE", "seats_for_sale": "22", "flight_count": 1 } ]
SELECT CONCAT(origin_0."code",' - ',origin_0."city") as "origin_name", COALESCE(SUM(aircraft_models_0."seats"),0) as "seats_for_sale", COUNT(1) as "flight_count" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/airports.parquet' AS origin_0 ON origin_0."code"=base."origin" LEFT JOIN '../../documentation/data/aircraft.parquet' AS aircraft_0 ON aircraft_0."tail_num"=base."tail_num" LEFT JOIN '../../documentation/data/aircraft_models.parquet' AS aircraft_models_0 ON aircraft_models_0."aircraft_model_code"=aircraft_0."aircraft_model_code" GROUP BY 1 ORDER BY 2 desc NULLS LAST
Filtering
Isolating the data to analyze is a big part of working with data. Let's limit our analysis to California airports. We've reformated the query. In Malloy, spaces and newlines are the same thing.
run: flights -> origin_name + seats_for_sale + flight_count + {where: origin.state='CA'}
[ { "origin_name": "LAX - LOS ANGELES", "seats_for_sale": "1936876", "flight_count": 11077 }, { "origin_name": "SFO - SAN FRANCISCO", "seats_for_sale": "848509", "flight_count": 4540 }, { "origin_name": "SAN - SAN DIEGO", "seats_for_sale": "823100", "flight_count": 5075 }, { "origin_name": "OAK - OAKLAND", "seats_for_sale": "775116", "flight_count": 5076 }, { "origin_name": "SJC - SAN JOSE", "seats_for_sale": "601204", "flight_count": 3825 }, { "origin_name": "SMF - SACRAMENTO", "seats_for_sale": "546267", "flight_count": 3576 }, { "origin_name": "ONT - ONTARIO", "seats_for_sale": "411496", "flight_count": 2642 }, { "origin_name": "SNA - SANTA ANA", "seats_for_sale": "302956", "flight_count": 1919 }, { "origin_name": "BUR - BURBANK", "seats_for_sale": "295747", "flight_count": 1952 }, { "origin_name": "LGB - LONG BEACH", "seats_for_sale": "125638", "flight_count": 661 }, { "origin_name": "PSP - PALM SPRINGS", "seats_for_sale": "39058", "flight_count": 240 }, { "origin_name": "FAT - FRESNO", "seats_for_sale": "9804", "flight_count": 57 }, { "origin_name": "MRY - MONTEREY", "seats_for_sale": "535", "flight_count": 3 }, { "origin_name": "BFL - BAKERSFIELD", "seats_for_sale": "495", "flight_count": 9 }, { "origin_name": "SBA - SANTA BARBARA", "seats_for_sale": "429", "flight_count": 18 } ]
SELECT CONCAT(origin_0."code",' - ',origin_0."city") as "origin_name", COALESCE(SUM(aircraft_models_0."seats"),0) as "seats_for_sale", COUNT(1) as "flight_count" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/airports.parquet' AS origin_0 ON origin_0."code"=base."origin" LEFT JOIN '../../documentation/data/aircraft.parquet' AS aircraft_0 ON aircraft_0."tail_num"=base."tail_num" LEFT JOIN '../../documentation/data/aircraft_models.parquet' AS aircraft_models_0 ON aircraft_models_0."aircraft_model_code"=aircraft_0."aircraft_model_code" WHERE origin_0."state"='CA' GROUP BY 1 ORDER BY 2 desc NULLS LAST
Adding your own measures
The Malloy semantic data model provides most of what we would like to calculate, but we can provide our own calculations in our queries. This model doesn't contain an average_distance, so we can compute this ourselves.
It looks like Jetblue, on average, has the longest flights.
You may also notice that by default, Malloy sorts results in descending order by the first measure.
run: flights -> carrier_name + {aggregate: avg_distance is distance.avg()} + flight_count
[ { "carrier_name": "Jetblue", "avg_distance": 1332.5667079719124, "flight_count": 4842 }, { "carrier_name": "United", "avg_distance": 1187.0114479347926, "flight_count": 32757 }, { "carrier_name": "American", "avg_distance": 1089.8830147207682, "flight_count": 34577 }, { "carrier_name": "Northwest", "avg_distance": 993.9399344848124, "flight_count": 33580 }, { "carrier_name": "ATA", "avg_distance": 872.1486976590834, "flight_count": 3033 }, { "carrier_name": "Alaska", "avg_distance": 836.679403761978, "flight_count": 8453 }, { "carrier_name": "Continental", "avg_distance": 811.6783863286174, "flight_count": 7139 }, { "carrier_name": "Delta", "avg_distance": 670.6465608465609, "flight_count": 32130 }, { "carrier_name": "America West", "avg_distance": 660.6878974358974, "flight_count": 9750 }, { "carrier_name": "USAir", "avg_distance": 629.5051349414855, "flight_count": 37683 }, { "carrier_name": "Southwest", "avg_distance": 615.4201304774031, "flight_count": 88751 }, { "carrier_name": "Continental Express", "avg_distance": 477.58902575587905, "flight_count": 16074 }, { "carrier_name": "Comair", "avg_distance": 451.8911764705882, "flight_count": 4420 }, { "carrier_name": "American Eagle", "avg_distance": 267.90969815363286, "flight_count": 15869 }, { "carrier_name": "Atlantic Southeast", "avg_distance": 201.17800748303634, "flight_count": 15769 } ]
SELECT carriers_0."nickname" as "carrier_name", AVG(base."distance") as "avg_distance", COUNT(1) as "flight_count" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/carriers.parquet' AS carriers_0 ON carriers_0."code"=base."carrier" GROUP BY 1 ORDER BY 2 desc NULLS LAST
Changing the sort order
We can change the sort order by adding a {order_by: } clause
run: flights -> carrier_name + {aggregate: avg_distance is distance.avg()} + flight_count + {order_by: flight_count desc}
[ { "carrier_name": "Southwest", "avg_distance": 615.4201304774031, "flight_count": 88751 }, { "carrier_name": "USAir", "avg_distance": 629.5051349414855, "flight_count": 37683 }, { "carrier_name": "American", "avg_distance": 1089.8830147207682, "flight_count": 34577 }, { "carrier_name": "Northwest", "avg_distance": 993.9399344848124, "flight_count": 33580 }, { "carrier_name": "United", "avg_distance": 1187.0114479347926, "flight_count": 32757 }, { "carrier_name": "Delta", "avg_distance": 670.6465608465609, "flight_count": 32130 }, { "carrier_name": "Continental Express", "avg_distance": 477.58902575587905, "flight_count": 16074 }, { "carrier_name": "American Eagle", "avg_distance": 267.90969815363286, "flight_count": 15869 }, { "carrier_name": "Atlantic Southeast", "avg_distance": 201.17800748303634, "flight_count": 15769 }, { "carrier_name": "America West", "avg_distance": 660.6878974358974, "flight_count": 9750 }, { "carrier_name": "Alaska", "avg_distance": 836.679403761978, "flight_count": 8453 }, { "carrier_name": "Continental", "avg_distance": 811.6783863286174, "flight_count": 7139 }, { "carrier_name": "Jetblue", "avg_distance": 1332.5667079719124, "flight_count": 4842 }, { "carrier_name": "Comair", "avg_distance": 451.8911764705882, "flight_count": 4420 }, { "carrier_name": "ATA", "avg_distance": 872.1486976590834, "flight_count": 3033 } ]
SELECT carriers_0."nickname" as "carrier_name", AVG(base."distance") as "avg_distance", COUNT(1) as "flight_count" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/carriers.parquet' AS carriers_0 ON carriers_0."code"=base."carrier" GROUP BY 1 ORDER BY 3 desc NULLS LAST
Adding your own dimensions
You can group by an expression. The expressions can contain just about any calculation you can do in SQL.
run: flights -> {group_by: carrier is concat(carrier, ' - ', carrier_name)} + origin_count + flight_count
[ { "carrier": "AA - American", "origin_count": 89, "flight_count": 34577 }, { "carrier": "AS - Alaska", "origin_count": 22, "flight_count": 8453 }, { "carrier": "B6 - Jetblue", "origin_count": 32, "flight_count": 4842 }, { "carrier": "CO - Continental", "origin_count": 78, "flight_count": 7139 }, { "carrier": "DL - Delta", "origin_count": 97, "flight_count": 32130 }, { "carrier": "EV - Atlantic Southeast", "origin_count": 59, "flight_count": 15769 }, { "carrier": "HP - America West", "origin_count": 41, "flight_count": 9750 }, { "carrier": "MQ - American Eagle", "origin_count": 77, "flight_count": 15869 }, { "carrier": "NW - Northwest", "origin_count": 88, "flight_count": 33580 }, { "carrier": "OH - Comair", "origin_count": 120, "flight_count": 4420 }, { "carrier": "RU - Continental Express", "origin_count": 126, "flight_count": 16074 }, { "carrier": "TZ - ATA", "origin_count": 25, "flight_count": 3033 }, { "carrier": "UA - United", "origin_count": 89, "flight_count": 32757 }, { "carrier": "US - USAir", "origin_count": 82, "flight_count": 37683 }, { "carrier": "WN - Southwest", "origin_count": 63, "flight_count": 88751 } ]
SELECT CONCAT(base."carrier",' - ',(carriers_0."nickname")) as "carrier", COUNT(DISTINCT origin_0."code") as "origin_count", COUNT(1) as "flight_count" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/carriers.parquet' AS carriers_0 ON carriers_0."code"=base."carrier" LEFT JOIN '../../documentation/data/airports.parquet' AS origin_0 ON origin_0."code"=base."origin" GROUP BY 1 ORDER BY 1 asc NULLS LAST
Working with Time
A big part of working with data is working with time. Queries that have time in the first column are sorted in descending order by time.
run: flights -> {group_by: flight_month is dep_time.month} + flight_count + {where: carrier = 'WN'}
[ { "flight_month": "2005-12-01T00:00:00.000Z", "flight_count": 1606 }, { "flight_month": "2005-11-01T00:00:00.000Z", "flight_count": 1448 }, { "flight_month": "2005-10-01T00:00:00.000Z", "flight_count": 1543 }, { "flight_month": "2005-09-01T00:00:00.000Z", "flight_count": 1466 }, { "flight_month": "2005-08-01T00:00:00.000Z", "flight_count": 1582 }, { "flight_month": "2005-07-01T00:00:00.000Z", "flight_count": 1564 }, { "flight_month": "2005-06-01T00:00:00.000Z", "flight_count": 1383 }, { "flight_month": "2005-05-01T00:00:00.000Z", "flight_count": 1384 }, { "flight_month": "2005-04-01T00:00:00.000Z", "flight_count": 1432 }, { "flight_month": "2005-03-01T00:00:00.000Z", "flight_count": 1439 }, { "flight_month": "2005-02-01T00:00:00.000Z", "flight_count": 1281 }, { "flight_month": "2005-01-01T00:00:00.000Z", "flight_count": 1421 }, { "flight_month": "2004-12-01T00:00:00.000Z", "flight_count": 1408 }, { "flight_month": "2004-11-01T00:00:00.000Z", "flight_count": 1370 }, { "flight_month": "2004-10-01T00:00:00.000Z", "flight_count": 1421 }, { "flight_month": "2004-09-01T00:00:00.000Z", "flight_count": 1305 }, { "flight_month": "2004-08-01T00:00:00.000Z", "flight_count": 1154 }, { "flight_month": "2004-07-01T00:00:00.000Z", "flight_count": 990 }, { "flight_month": "2004-06-01T00:00:00.000Z", "flight_count": 1088 }, { "flight_month": "2004-05-01T00:00:00.000Z", "flight_count": 1225 }, { "flight_month": "2004-04-01T00:00:00.000Z", "flight_count": 1213 }, { "flight_month": "2004-03-01T00:00:00.000Z", "flight_count": 1197 }, { "flight_month": "2004-02-01T00:00:00.000Z", "flight_count": 1120 }, { "flight_month": "2004-01-01T00:00:00.000Z", "flight_count": 1149 }, { "flight_month": "2003-12-01T00:00:00.000Z", "flight_count": 1205 }, { "flight_month": "2003-11-01T00:00:00.000Z", "flight_count": 1137 }, { "flight_month": "2003-10-01T00:00:00.000Z", "flight_count": 1160 }, { "flight_month": "2003-09-01T00:00:00.000Z", "flight_count": 1199 }, { "flight_month": "2003-08-01T00:00:00.000Z", "flight_count": 1219 }, { "flight_month": "2003-07-01T00:00:00.000Z", "flight_count": 1252 }, { "flight_month": "2003-06-01T00:00:00.000Z", "flight_count": 1205 }, { "flight_month": "2003-05-01T00:00:00.000Z", "flight_count": 1232 }, { "flight_month": "2003-04-01T00:00:00.000Z", "flight_count": 1193 }, { "flight_month": "2003-03-01T00:00:00.000Z", "flight_count": 1191 }, { "flight_month": "2003-02-01T00:00:00.000Z", "flight_count": 1098 }, { "flight_month": "2003-01-01T00:00:00.000Z", "flight_count": 1209 }, { "flight_month": "2002-12-01T00:00:00.000Z", "flight_count": 1250 }, { "flight_month": "2002-11-01T00:00:00.000Z", "flight_count": 1179 }, { "flight_month": "2002-10-01T00:00:00.000Z", "flight_count": 1280 }, { "flight_month": "2002-09-01T00:00:00.000Z", "flight_count": 1214 }, { "flight_month": "2002-08-01T00:00:00.000Z", "flight_count": 1286 }, { "flight_month": "2002-07-01T00:00:00.000Z", "flight_count": 1235 }, { "flight_month": "2002-06-01T00:00:00.000Z", "flight_count": 1215 }, { "flight_month": "2002-05-01T00:00:00.000Z", "flight_count": 1291 }, { "flight_month": "2002-04-01T00:00:00.000Z", "flight_count": 1186 }, { "flight_month": "2002-03-01T00:00:00.000Z", "flight_count": 1240 }, { "flight_month": "2002-02-01T00:00:00.000Z", "flight_count": 1092 }, { "flight_month": "2002-01-01T00:00:00.000Z", "flight_count": 1240 }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 1159 }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 1224 }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 1309 }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 1150 }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 1318 }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 1299 }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 1254 }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 1216 }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 1218 }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 747 }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 1212 }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 1315 }, { "flight_month": "2000-12-01T00:00:00.000Z", "flight_count": 1116 }, { "flight_month": "2000-11-01T00:00:00.000Z", "flight_count": 1059 }, { "flight_month": "2000-10-01T00:00:00.000Z", "flight_count": 1108 }, { "flight_month": "2000-09-01T00:00:00.000Z", "flight_count": 1040 }, { "flight_month": "2000-08-01T00:00:00.000Z", "flight_count": 1143 }, { "flight_month": "2000-07-01T00:00:00.000Z", "flight_count": 1070 }, { "flight_month": "2000-06-01T00:00:00.000Z", "flight_count": 1030 }, { "flight_month": "2000-05-01T00:00:00.000Z", "flight_count": 1151 }, { "flight_month": "2000-04-01T00:00:00.000Z", "flight_count": 1097 }, { "flight_month": "2000-03-01T00:00:00.000Z", "flight_count": 1117 }, { "flight_month": "2000-02-01T00:00:00.000Z", "flight_count": 1088 }, { "flight_month": "2000-01-01T00:00:00.000Z", "flight_count": 1114 } ]
SELECT DATE_TRUNC('month', base."dep_time") as "flight_month", COUNT(1) as "flight_count" FROM '../../documentation/data/flights.parquet' as base WHERE base."carrier"='WN' GROUP BY 1 ORDER BY 1 desc NULLS LAST
Annotations and Charting
Charts are generally another view on tables. For example, the table above can be viewed as a line chart. The x-axis is flight_month and the y-axis is flight_count. Malloy's annotations let you tag a query so the rendering engine can show the results in different ways. We simply tag the query above as a # line_chart.
# line_chart run: flights -> {group_by: flight_month is dep_time.month} + flight_count + {where: carrier = 'WN'}
[ { "flight_month": "2005-12-01T00:00:00.000Z", "flight_count": 1606 }, { "flight_month": "2005-11-01T00:00:00.000Z", "flight_count": 1448 }, { "flight_month": "2005-10-01T00:00:00.000Z", "flight_count": 1543 }, { "flight_month": "2005-09-01T00:00:00.000Z", "flight_count": 1466 }, { "flight_month": "2005-08-01T00:00:00.000Z", "flight_count": 1582 }, { "flight_month": "2005-07-01T00:00:00.000Z", "flight_count": 1564 }, { "flight_month": "2005-06-01T00:00:00.000Z", "flight_count": 1383 }, { "flight_month": "2005-05-01T00:00:00.000Z", "flight_count": 1384 }, { "flight_month": "2005-04-01T00:00:00.000Z", "flight_count": 1432 }, { "flight_month": "2005-03-01T00:00:00.000Z", "flight_count": 1439 }, { "flight_month": "2005-02-01T00:00:00.000Z", "flight_count": 1281 }, { "flight_month": "2005-01-01T00:00:00.000Z", "flight_count": 1421 }, { "flight_month": "2004-12-01T00:00:00.000Z", "flight_count": 1408 }, { "flight_month": "2004-11-01T00:00:00.000Z", "flight_count": 1370 }, { "flight_month": "2004-10-01T00:00:00.000Z", "flight_count": 1421 }, { "flight_month": "2004-09-01T00:00:00.000Z", "flight_count": 1305 }, { "flight_month": "2004-08-01T00:00:00.000Z", "flight_count": 1154 }, { "flight_month": "2004-07-01T00:00:00.000Z", "flight_count": 990 }, { "flight_month": "2004-06-01T00:00:00.000Z", "flight_count": 1088 }, { "flight_month": "2004-05-01T00:00:00.000Z", "flight_count": 1225 }, { "flight_month": "2004-04-01T00:00:00.000Z", "flight_count": 1213 }, { "flight_month": "2004-03-01T00:00:00.000Z", "flight_count": 1197 }, { "flight_month": "2004-02-01T00:00:00.000Z", "flight_count": 1120 }, { "flight_month": "2004-01-01T00:00:00.000Z", "flight_count": 1149 }, { "flight_month": "2003-12-01T00:00:00.000Z", "flight_count": 1205 }, { "flight_month": "2003-11-01T00:00:00.000Z", "flight_count": 1137 }, { "flight_month": "2003-10-01T00:00:00.000Z", "flight_count": 1160 }, { "flight_month": "2003-09-01T00:00:00.000Z", "flight_count": 1199 }, { "flight_month": "2003-08-01T00:00:00.000Z", "flight_count": 1219 }, { "flight_month": "2003-07-01T00:00:00.000Z", "flight_count": 1252 }, { "flight_month": "2003-06-01T00:00:00.000Z", "flight_count": 1205 }, { "flight_month": "2003-05-01T00:00:00.000Z", "flight_count": 1232 }, { "flight_month": "2003-04-01T00:00:00.000Z", "flight_count": 1193 }, { "flight_month": "2003-03-01T00:00:00.000Z", "flight_count": 1191 }, { "flight_month": "2003-02-01T00:00:00.000Z", "flight_count": 1098 }, { "flight_month": "2003-01-01T00:00:00.000Z", "flight_count": 1209 }, { "flight_month": "2002-12-01T00:00:00.000Z", "flight_count": 1250 }, { "flight_month": "2002-11-01T00:00:00.000Z", "flight_count": 1179 }, { "flight_month": "2002-10-01T00:00:00.000Z", "flight_count": 1280 }, { "flight_month": "2002-09-01T00:00:00.000Z", "flight_count": 1214 }, { "flight_month": "2002-08-01T00:00:00.000Z", "flight_count": 1286 }, { "flight_month": "2002-07-01T00:00:00.000Z", "flight_count": 1235 }, { "flight_month": "2002-06-01T00:00:00.000Z", "flight_count": 1215 }, { "flight_month": "2002-05-01T00:00:00.000Z", "flight_count": 1291 }, { "flight_month": "2002-04-01T00:00:00.000Z", "flight_count": 1186 }, { "flight_month": "2002-03-01T00:00:00.000Z", "flight_count": 1240 }, { "flight_month": "2002-02-01T00:00:00.000Z", "flight_count": 1092 }, { "flight_month": "2002-01-01T00:00:00.000Z", "flight_count": 1240 }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 1159 }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 1224 }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 1309 }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 1150 }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 1318 }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 1299 }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 1254 }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 1216 }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 1218 }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 747 }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 1212 }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 1315 }, { "flight_month": "2000-12-01T00:00:00.000Z", "flight_count": 1116 }, { "flight_month": "2000-11-01T00:00:00.000Z", "flight_count": 1059 }, { "flight_month": "2000-10-01T00:00:00.000Z", "flight_count": 1108 }, { "flight_month": "2000-09-01T00:00:00.000Z", "flight_count": 1040 }, { "flight_month": "2000-08-01T00:00:00.000Z", "flight_count": 1143 }, { "flight_month": "2000-07-01T00:00:00.000Z", "flight_count": 1070 }, { "flight_month": "2000-06-01T00:00:00.000Z", "flight_count": 1030 }, { "flight_month": "2000-05-01T00:00:00.000Z", "flight_count": 1151 }, { "flight_month": "2000-04-01T00:00:00.000Z", "flight_count": 1097 }, { "flight_month": "2000-03-01T00:00:00.000Z", "flight_count": 1117 }, { "flight_month": "2000-02-01T00:00:00.000Z", "flight_count": 1088 }, { "flight_month": "2000-01-01T00:00:00.000Z", "flight_count": 1114 } ]
SELECT DATE_TRUNC('month', base."dep_time") as "flight_month", COUNT(1) as "flight_count" FROM '../../documentation/data/flights.parquet' as base WHERE base."carrier"='WN' GROUP BY 1 ORDER BY 1 desc NULLS LAST
Filtering Time
Filtering time ranges is always difficult in SQL. Malloy time ranges can be specified simply. We add a filter to limit the time range to the year 2001.
# line_chart run: flights -> {group_by: flight_month is dep_time.month} + flight_count + carrier_name + {where: dep_time ? @2001}
[ { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 403, "carrier_name": "Delta" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 486, "carrier_name": "American" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 108, "carrier_name": "Alaska" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 84, "carrier_name": "Continental" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 432, "carrier_name": "Northwest" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 115, "carrier_name": "America West" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 634, "carrier_name": "USAir" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 247, "carrier_name": "American Eagle" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 1159, "carrier_name": "Southwest" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 418, "carrier_name": "United" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 462, "carrier_name": "American" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 212, "carrier_name": "American Eagle" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 52, "carrier_name": "America West" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 101, "carrier_name": "Continental" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 113, "carrier_name": "Alaska" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 1224, "carrier_name": "Southwest" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 399, "carrier_name": "United" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 440, "carrier_name": "Delta" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 499, "carrier_name": "USAir" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 317, "carrier_name": "Northwest" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 110, "carrier_name": "Continental" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 94, "carrier_name": "America West" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 98, "carrier_name": "Alaska" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 455, "carrier_name": "American" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 456, "carrier_name": "USAir" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 1309, "carrier_name": "Southwest" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 290, "carrier_name": "American Eagle" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 346, "carrier_name": "Delta" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 361, "carrier_name": "United" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 290, "carrier_name": "Northwest" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 76, "carrier_name": "Continental" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 419, "carrier_name": "Northwest" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 67, "carrier_name": "Alaska" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 135, "carrier_name": "America West" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 347, "carrier_name": "American" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 449, "carrier_name": "USAir" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 450, "carrier_name": "Delta" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 227, "carrier_name": "American Eagle" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 1150, "carrier_name": "Southwest" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 296, "carrier_name": "United" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 557, "carrier_name": "American" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 554, "carrier_name": "USAir" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 116, "carrier_name": "Continental" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 516, "carrier_name": "Northwest" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 153, "carrier_name": "America West" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 1318, "carrier_name": "Southwest" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 343, "carrier_name": "American Eagle" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 554, "carrier_name": "Delta" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 363, "carrier_name": "United" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 133, "carrier_name": "Alaska" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 367, "carrier_name": "American Eagle" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 522, "carrier_name": "American" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 142, "carrier_name": "America West" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 498, "carrier_name": "Delta" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 123, "carrier_name": "Continental" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 491, "carrier_name": "Northwest" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 143, "carrier_name": "Alaska" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 583, "carrier_name": "USAir" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 1299, "carrier_name": "Southwest" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 272, "carrier_name": "United" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 485, "carrier_name": "Delta" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 512, "carrier_name": "Northwest" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 543, "carrier_name": "American" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 160, "carrier_name": "America West" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 110, "carrier_name": "Continental" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 133, "carrier_name": "Alaska" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 607, "carrier_name": "USAir" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 338, "carrier_name": "United" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 1254, "carrier_name": "Southwest" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 309, "carrier_name": "American Eagle" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 535, "carrier_name": "Delta" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 491, "carrier_name": "Northwest" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 151, "carrier_name": "America West" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 139, "carrier_name": "Alaska" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 124, "carrier_name": "Continental" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 312, "carrier_name": "United" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 466, "carrier_name": "American" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 327, "carrier_name": "American Eagle" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 646, "carrier_name": "USAir" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 1216, "carrier_name": "Southwest" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 277, "carrier_name": "United" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 120, "carrier_name": "Alaska" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 478, "carrier_name": "Northwest" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 305, "carrier_name": "American Eagle" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 1218, "carrier_name": "Southwest" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 641, "carrier_name": "USAir" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 553, "carrier_name": "Delta" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 101, "carrier_name": "America West" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 112, "carrier_name": "Continental" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 523, "carrier_name": "American" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 478, "carrier_name": "Northwest" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 127, "carrier_name": "Alaska" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 589, "carrier_name": "USAir" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 747, "carrier_name": "Southwest" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 332, "carrier_name": "American Eagle" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 589, "carrier_name": "Delta" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 150, "carrier_name": "America West" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 25, "carrier_name": "Continental" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 514, "carrier_name": "American" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 318, "carrier_name": "United" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 154, "carrier_name": "America West" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 443, "carrier_name": "American" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 1212, "carrier_name": "Southwest" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 230, "carrier_name": "United" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 552, "carrier_name": "USAir" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 387, "carrier_name": "Delta" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 464, "carrier_name": "Northwest" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 92, "carrier_name": "Continental" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 138, "carrier_name": "Alaska" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 184, "carrier_name": "America West" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 493, "carrier_name": "Northwest" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 134, "carrier_name": "Continental" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 118, "carrier_name": "Alaska" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 533, "carrier_name": "American" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 1315, "carrier_name": "Southwest" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 656, "carrier_name": "USAir" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 201, "carrier_name": "United" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 437, "carrier_name": "Delta" } ]
SELECT DATE_TRUNC('month', base."dep_time") as "flight_month", COUNT(1) as "flight_count", carriers_0."nickname" as "carrier_name" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/carriers.parquet' AS carriers_0 ON carriers_0."code"=base."carrier" 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 desc NULLS LAST
Views: Pre-built Queries
Semantic models can include views. Views are pre-built queries. Often in a dataset there are several interesting ways of looking at the dataset. A common use for views is to declare these in advance.
run: flights -> by_carrier[ { "carrier": "AA - American", "flight_count": 34577, "total_distance": "37684885", "percent_of_flights": 0.10027347046489979 }, { "carrier": "AS - Alaska", "flight_count": 8453, "total_distance": "7072451", "percent_of_flights": 0.024513741673360845 }, { "carrier": "B6 - Jetblue", "flight_count": 4842, "total_distance": "6452288", "percent_of_flights": 0.01404182387110058 }, { "carrier": "CO - Continental", "flight_count": 7139, "total_distance": "5794572", "percent_of_flights": 0.020703135195329833 }, { "carrier": "DL - Delta", "flight_count": 32130, "total_distance": "21547874", "percent_of_flights": 0.09317715840116929 }, { "carrier": "EV - Atlantic Southeast", "flight_count": 15769, "total_distance": "3172376", "percent_of_flights": 0.04573017774130216 }, { "carrier": "HP - America West", "flight_count": 9750, "total_distance": "6441707", "percent_of_flights": 0.028275048067581715 }, { "carrier": "MQ - American Eagle", "flight_count": 15869, "total_distance": "4251459", "percent_of_flights": 0.046020178234302996 }, { "carrier": "NW - Northwest", "flight_count": 33580, "total_distance": "33376503", "percent_of_flights": 0.09738216554968143 }, { "carrier": "OH - Comair", "flight_count": 4420, "total_distance": "1997359", "percent_of_flights": 0.012818021790637044 }, { "carrier": "RU - Continental Express", "flight_count": 16074, "total_distance": "7676766", "percent_of_flights": 0.046614679244954715 }, { "carrier": "TZ - ATA", "flight_count": 3033, "total_distance": "2645227", "percent_of_flights": 0.00879571495271542 }, { "carrier": "UA - United", "flight_count": 32757, "total_distance": "38882934", "percent_of_flights": 0.09499546149228454 }, { "carrier": "US - USAir", "flight_count": 37683, "total_distance": "23721642", "percent_of_flights": 0.10928088577750582 }, { "carrier": "WN - Southwest", "flight_count": 88751, "total_distance": "54619152", "percent_of_flights": 0.2573783375431738 } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set=1 THEN CONCAT(base."carrier",' - ',(carriers_0."nickname")) END as "carrier__1", CASE WHEN group_set=1 THEN COUNT(1) END as "flight_count__1", CASE WHEN group_set=1 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__1", (CASE WHEN group_set=1 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=0 THEN COUNT(1) END)) OVER () as "percent_of_flights__1" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/carriers.parquet' AS carriers_0 ON carriers_0."code"=base."carrier" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1,2 ) SELECT "carrier__1" as "carrier", MAX(CASE WHEN group_set=1 THEN "flight_count__1" END) as "flight_count", MAX(CASE WHEN group_set=1 THEN "total_distance__1" END) as "total_distance", MAX(CASE WHEN group_set=1 THEN "percent_of_flights__1" END) as "percent_of_flights" FROM __stage0 WHERE group_set NOT IN (0) GROUP BY 1 ORDER BY 1 asc NULLS LAST
Convention: the 'metrics' view.
Malloy models often contain a metrics view. The metrics view contains the most common ways of measuring data in this data set. This makes it easy build queries on the fly.
run: flights -> metrics[ { "flight_count": 344827, "total_distance": "255337195", "percent_of_flights": 1 } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set=1 THEN COUNT(1) END as "flight_count__1", CASE WHEN group_set=1 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__1", (CASE WHEN group_set=1 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=0 THEN COUNT(1) END)) OVER () as "percent_of_flights__1" FROM '../../documentation/data/flights.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1 ) SELECT MAX(CASE WHEN group_set=1 THEN "flight_count__1" END) as "flight_count", MAX(CASE WHEN group_set=1 THEN "total_distance__1" END) as "total_distance", MAX(CASE WHEN group_set=1 THEN "percent_of_flights__1" END) as "percent_of_flights" FROM __stage0 WHERE group_set NOT IN (0)
Metrics by Origin
Views can be combined in queries just like all the other parts.
run: flights -> origin_name + metrics[ { "origin_name": "ATL - ATLANTA", "flight_count": 17875, "total_distance": "8722080", "percent_of_flights": 0.05183758812389981 }, { "origin_name": "DFW - DALLAS-FORT WORTH", "flight_count": 17782, "total_distance": "12292462", "percent_of_flights": 0.05156788766540903 }, { "origin_name": "ORD - CHICAGO", "flight_count": 14214, "total_distance": "14040186", "percent_of_flights": 0.041220670075139125 }, { "origin_name": "PHX - PHOENIX", "flight_count": 12476, "total_distance": "10310511", "percent_of_flights": 0.03618046150678456 }, { "origin_name": "LAS - LAS VEGAS", "flight_count": 11096, "total_distance": "8782093", "percent_of_flights": 0.032178454703372994 }, { "origin_name": "LAX - LOS ANGELES", "flight_count": 11077, "total_distance": "12861847", "percent_of_flights": 0.03212335460970284 }, { "origin_name": "MSP - MINNEAPOLIS", "flight_count": 9762, "total_distance": "9798857", "percent_of_flights": 0.028309848126741817 }, { "origin_name": "DTW - DETROIT", "flight_count": 8161, "total_distance": "6639449", "percent_of_flights": 0.023666940233798398 }, { "origin_name": "PHL - PHILADELPHIA", "flight_count": 7708, "total_distance": "5685111", "percent_of_flights": 0.0223532380005046 }, { "origin_name": "LGA - NEW YORK", "flight_count": 7623, "total_distance": "3991946", "percent_of_flights": 0.02210673758145389 }, { "origin_name": "DEN - DENVER", "flight_count": 7190, "total_distance": "7328763", "percent_of_flights": 0.02085103544676026 }, { "origin_name": "BWI - BALTIMORE", "flight_count": 7177, "total_distance": "4832242", "percent_of_flights": 0.02081333538267015 }, { "origin_name": "CLT - CHARLOTTE", "flight_count": 7099, "total_distance": "4010313", "percent_of_flights": 0.020587134998129496 }, { "origin_name": "SEA - SEATTLE", "flight_count": 7010, "total_distance": "7823835", "percent_of_flights": 0.02032903455935875 }, { "origin_name": "MCO - ORLANDO", "flight_count": 6790, "total_distance": "6330474", "percent_of_flights": 0.019691033474756908 }, { "origin_name": "DCA - WASHINGTON", "flight_count": 6678, "total_distance": "3093505", "percent_of_flights": 0.01936623292259597 }, { "origin_name": "IAH - HOUSTON", "flight_count": 6623, "total_distance": "5031322", "percent_of_flights": 0.019206732651445506 }, { "origin_name": "MDW - CHICAGO", "flight_count": 6611, "total_distance": "4795391", "percent_of_flights": 0.019171932592285407 }, { "origin_name": "BOS - BOSTON", "flight_count": 5797, "total_distance": "4481594", "percent_of_flights": 0.016811328579258586 }, { "origin_name": "EWR - NEWARK", "flight_count": 5174, "total_distance": "3930757", "percent_of_flights": 0.015004625507863363 }, { "origin_name": "CLE - CLEVELAND", "flight_count": 5127, "total_distance": "2430647", "percent_of_flights": 0.01486832527615297 }, { "origin_name": "OAK - OAKLAND", "flight_count": 5076, "total_distance": "3472237", "percent_of_flights": 0.014720425024722542 }, { "origin_name": "SAN - SAN DIEGO", "flight_count": 5075, "total_distance": "4691341", "percent_of_flights": 0.014717525019792534 }, { "origin_name": "TPA - TAMPA", "flight_count": 4868, "total_distance": "3912217", "percent_of_flights": 0.014117223999280799 }, { "origin_name": "PIT - PITTSBURGH", "flight_count": 4541, "total_distance": "3088305", "percent_of_flights": 0.013168922387168058 }, { "origin_name": "SFO - SAN FRANCISCO", "flight_count": 4540, "total_distance": "6391762", "percent_of_flights": 0.01316602238223805 }, { "origin_name": "BNA - NASHVILLE", "flight_count": 4287, "total_distance": "3192917", "percent_of_flights": 0.012432321134945929 }, { "origin_name": "STL - ST LOUIS", "flight_count": 4089, "total_distance": "2600889", "percent_of_flights": 0.01185812015880427 }, { "origin_name": "MCI - KANSAS CITY", "flight_count": 4078, "total_distance": "3259508", "percent_of_flights": 0.011826220104574178 }, { "origin_name": "IAD - WASHINGTON", "flight_count": 3885, "total_distance": "5360996", "percent_of_flights": 0.01126651915308256 }, { "origin_name": "SJC - SAN JOSE", "flight_count": 3825, "total_distance": "2826646", "percent_of_flights": 0.011092518857282057 }, { "origin_name": "JFK - NEW YORK", "flight_count": 3689, "total_distance": "4995032", "percent_of_flights": 0.010698118186800918 }, { "origin_name": "FLL - FORT LAUDERDALE", "flight_count": 3619, "total_distance": "3060811", "percent_of_flights": 0.010495117841700331 }, { "origin_name": "PDX - PORTLAND", "flight_count": 3596, "total_distance": "3486953", "percent_of_flights": 0.010428417728310138 }, { "origin_name": "SMF - SACRAMENTO", "flight_count": 3576, "total_distance": "2367376", "percent_of_flights": 0.01037041762970997 }, { "origin_name": "HOU - HOUSTON", "flight_count": 3430, "total_distance": "1910661", "percent_of_flights": 0.009947016909928746 }, { "origin_name": "CVG - COVINGTON/CINCINNATI, OH", "flight_count": 3300, "total_distance": "1881171", "percent_of_flights": 0.009570016269027657 }, { "origin_name": "MSY - NEW ORLEANS", "flight_count": 3254, "total_distance": "2384286", "percent_of_flights": 0.009436616042247272 }, { "origin_name": "ABQ - ALBUQUERQUE", "flight_count": 2762, "total_distance": "1842519", "percent_of_flights": 0.008009813616683148 }, { "origin_name": "SLC - SALT LAKE CITY", "flight_count": 2741, "total_distance": "2341768", "percent_of_flights": 0.007948913513152972 }, { "origin_name": "ONT - ONTARIO", "flight_count": 2642, "total_distance": "1492508", "percent_of_flights": 0.007661813025082143 }, { "origin_name": "RDU - RALEIGH/DURHAM", "flight_count": 2444, "total_distance": "1344268", "percent_of_flights": 0.007087612048940483 }, { "origin_name": "MIA - MIAMI", "flight_count": 2387, "total_distance": "2711298", "percent_of_flights": 0.006922311767930006 }, { "origin_name": "PVD - PROVIDENCE", "flight_count": 2361, "total_distance": "1657447", "percent_of_flights": 0.006846911639749788 }, { "origin_name": "MEM - MEMPHIS", "flight_count": 2242, "total_distance": "1929191", "percent_of_flights": 0.00650181105307879 }, { "origin_name": "IND - INDIANAPOLIS", "flight_count": 2226, "total_distance": "1476002", "percent_of_flights": 0.006455410974198656 }, { "origin_name": "BDL - WINDSOR LOCKS", "flight_count": 2168, "total_distance": "1549166", "percent_of_flights": 0.00628721068825817 }, { "origin_name": "AUS - AUSTIN", "flight_count": 2091, "total_distance": "1377592", "percent_of_flights": 0.006063910308647525 }, { "origin_name": "RNO - RENO", "flight_count": 1992, "total_distance": "1024720", "percent_of_flights": 0.005776809820576695 }, { "origin_name": "BUR - BURBANK", "flight_count": 1952, "total_distance": "876479", "percent_of_flights": 0.005660809623376359 }, { "origin_name": "SNA - SANTA ANA", "flight_count": 1919, "total_distance": "1377110", "percent_of_flights": 0.005565109460686083 }, { "origin_name": "SAT - SAN ANTONIO", "flight_count": 1865, "total_distance": "1190271", "percent_of_flights": 0.005408509194465631 }, { "origin_name": "CMH - COLUMBUS", "flight_count": 1712, "total_distance": "961555", "percent_of_flights": 0.004964808440174349 }, { "origin_name": "DAL - DALLAS", "flight_count": 1662, "total_distance": "532463", "percent_of_flights": 0.004819808193673929 }, { "origin_name": "MHT - MANCHESTER", "flight_count": 1626, "total_distance": "1019366", "percent_of_flights": 0.004715408016193628 }, { "origin_name": "JAX - JACKSONVILLE", "flight_count": 1599, "total_distance": "850962", "percent_of_flights": 0.004637107883083401 }, { "origin_name": "ELP - EL PASO", "flight_count": 1438, "total_distance": "752719", "percent_of_flights": 0.004170207089352052 }, { "origin_name": "PBI - WEST PALM BEACH", "flight_count": 1379, "total_distance": "1193311", "percent_of_flights": 0.0039991067984815575 }, { "origin_name": "ISP - ISLIP", "flight_count": 1302, "total_distance": "839548", "percent_of_flights": 0.003775806418870912 }, { "origin_name": "BUF - BUFFALO", "flight_count": 1246, "total_distance": "613873", "percent_of_flights": 0.0036134061427904427 }, { "origin_name": "BHM - BIRMINGHAM", "flight_count": 1200, "total_distance": "640507", "percent_of_flights": 0.0034800059160100573 }, { "origin_name": "RSW - FORT MYERS", "flight_count": 1169, "total_distance": "1064941", "percent_of_flights": 0.0033901057631797976 }, { "origin_name": "MKE - MILWAUKEE", "flight_count": 1132, "total_distance": "646507", "percent_of_flights": 0.0032828055807694874 }, { "origin_name": "ALB - ALBANY", "flight_count": 1108, "total_distance": "584503", "percent_of_flights": 0.003213205462449286 }, { "origin_name": "TUS - TUCSON", "flight_count": 1062, "total_distance": "671325", "percent_of_flights": 0.0030798052356689008 }, { "origin_name": "SDF - LOUISVILLE", "flight_count": 1003, "total_distance": "526947", "percent_of_flights": 0.002908704944798406 }, { "origin_name": "TUL - TULSA", "flight_count": 984, "total_distance": "427576", "percent_of_flights": 0.002853604851128247 }, { "origin_name": "RIC - RICHMOND", "flight_count": 946, "total_distance": "378039", "percent_of_flights": 0.0027434046637879283 }, { "origin_name": "OMA - OMAHA", "flight_count": 946, "total_distance": "630528", "percent_of_flights": 0.0027434046637879283 }, { "origin_name": "GEG - SPOKANE", "flight_count": 922, "total_distance": "426990", "percent_of_flights": 0.002673804545467727 }, { "origin_name": "OKC - OKLAHOMA CITY", "flight_count": 905, "total_distance": "406968", "percent_of_flights": 0.0026245044616575847 }, { "origin_name": "ORF - NORFOLK", "flight_count": 888, "total_distance": "447531", "percent_of_flights": 0.0025752043778474423 }, { "origin_name": "LIT - LITTLE ROCK", "flight_count": 853, "total_distance": "382625", "percent_of_flights": 0.002473704205297149 }, { "origin_name": "BOI - BOISE", "flight_count": 795, "total_distance": "424168", "percent_of_flights": 0.002305503919356663 }, { "origin_name": "PFN - PANAMA CITY", "flight_count": 778, "total_distance": "192574", "percent_of_flights": 0.0022562038355465205 }, { "origin_name": "AGS - AUGUSTA", "flight_count": 756, "total_distance": "130284", "percent_of_flights": 0.002192403727086336 }, { "origin_name": "ANC - ANCHORAGE", "flight_count": 748, "total_distance": "1303817", "percent_of_flights": 0.002169203687646269 }, { "origin_name": "ROC - ROCHESTER", "flight_count": 733, "total_distance": "250833", "percent_of_flights": 0.0021257036136961434 }, { "origin_name": "AVL - ASHEVILLE", "flight_count": 727, "total_distance": "131633", "percent_of_flights": 0.002108303584116093 }, { "origin_name": "CHA - CHATTANOOGA", "flight_count": 697, "total_distance": "78526", "percent_of_flights": 0.0020213034362158416 }, { "origin_name": "LGB - LONG BEACH", "flight_count": 661, "total_distance": "931914", "percent_of_flights": 0.00191690325873554 }, { "origin_name": "CSG - COLUMBUS", "flight_count": 654, "total_distance": "54282", "percent_of_flights": 0.0018966032242254812 }, { "origin_name": "SJU - SAN JUAN", "flight_count": 645, "total_distance": "1115561", "percent_of_flights": 0.0018705031798554057 }, { "origin_name": "MYR - MYRTLE BEACH", "flight_count": 628, "total_distance": "167934", "percent_of_flights": 0.0018212030960452633 }, { "origin_name": "HSV - HUNTSVILLE", "flight_count": 607, "total_distance": "132372", "percent_of_flights": 0.0017603029925150873 }, { "origin_name": "SYR - SYRACUSE", "flight_count": 597, "total_distance": "214101", "percent_of_flights": 0.0017313029432150034 }, { "origin_name": "MDT - HARRISBURG", "flight_count": 584, "total_distance": "190637", "percent_of_flights": 0.0016936028791248944 }, { "origin_name": "TRI - BRISTOL/JOHNSON/KINGSPORT", "flight_count": 566, "total_distance": "127960", "percent_of_flights": 0.0016414027903847437 }, { "origin_name": "GRR - GRAND RAPIDS", "flight_count": 513, "total_distance": "139776", "percent_of_flights": 0.0014877025290942994 }, { "origin_name": "ABE - ALLENTOWN", "flight_count": 510, "total_distance": "207841", "percent_of_flights": 0.0014790025143042744 }, { "origin_name": "MGM - MONTGOMERY", "flight_count": 500, "total_distance": "73500", "percent_of_flights": 0.0014500024650041905 }, { "origin_name": "ACT - WACO", "flight_count": 494, "total_distance": "43966", "percent_of_flights": 0.0014326024354241402 }, { "origin_name": "TYR - TYLER", "flight_count": 480, "total_distance": "49440", "percent_of_flights": 0.0013920023664040228 }, { "origin_name": "GSO - GREENSBORO", "flight_count": 470, "total_distance": "174414", "percent_of_flights": 0.0013630023171039391 }, { "origin_name": "GNV - GAINESVILLE", "flight_count": 470, "total_distance": "140922", "percent_of_flights": 0.0013630023171039391 }, { "origin_name": "CHS - CHARLESTON", "flight_count": 466, "total_distance": "172485", "percent_of_flights": 0.0013514022973839055 }, { "origin_name": "TYS - KNOXVILLE", "flight_count": 464, "total_distance": "128227", "percent_of_flights": 0.0013456022875238888 }, { "origin_name": "SPS - WICHITA FALLS", "flight_count": 457, "total_distance": "51641", "percent_of_flights": 0.0013253022530138301 }, { "origin_name": "CLL - COLLEGE STATION", "flight_count": 454, "total_distance": "73916", "percent_of_flights": 0.0013166022382238049 }, { "origin_name": "COS - COLORADO SPRINGS", "flight_count": 426, "total_distance": "308835", "percent_of_flights": 0.0012354021001835702 }, { "origin_name": "LBB - LUBBOCK", "flight_count": 425, "total_distance": "153238", "percent_of_flights": 0.0012325020952535619 }, { "origin_name": "LAW - LAWTON", "flight_count": 423, "total_distance": "59220", "percent_of_flights": 0.0012267020853935452 }, { "origin_name": "SAV - SAVANNAH", "flight_count": 419, "total_distance": "150911", "percent_of_flights": 0.0012151020656735116 }, { "origin_name": "ILE - KILLEEN", "flight_count": 416, "total_distance": "54109", "percent_of_flights": 0.0012064020508834865 }, { "origin_name": "DAY - DAYTON", "flight_count": 416, "total_distance": "153036", "percent_of_flights": 0.0012064020508834865 }, { "origin_name": "MAF - MIDLAND", "flight_count": 409, "total_distance": "149176", "percent_of_flights": 0.0011861020163734279 }, { "origin_name": "SHV - SHREVEPORT", "flight_count": 406, "total_distance": "81776", "percent_of_flights": 0.0011774020015834026 }, { "origin_name": "VPS - VALPARAISO", "flight_count": 403, "total_distance": "110672", "percent_of_flights": 0.0011687019867933776 }, { "origin_name": "CAE - COLUMBIA", "flight_count": 376, "total_distance": "111548", "percent_of_flights": 0.0010904018536831513 }, { "origin_name": "JAN - JACKSON", "flight_count": 356, "total_distance": "189441", "percent_of_flights": 0.0010324017550829836 }, { "origin_name": "PWM - PORTLAND", "flight_count": 326, "total_distance": "124118", "percent_of_flights": 0.0009454016071827322 }, { "origin_name": "DHN - DOTHAN", "flight_count": 324, "total_distance": "55404", "percent_of_flights": 0.0009396015973227155 }, { "origin_name": "XNA - FAYETTEVILLE/SPRINGDALE/", "flight_count": 319, "total_distance": "154848", "percent_of_flights": 0.0009251015726726735 }, { "origin_name": "HPN - WHITE PLAINS", "flight_count": 315, "total_distance": "141252", "percent_of_flights": 0.00091350155295264 }, { "origin_name": "BTV - BURLINGTON", "flight_count": 307, "total_distance": "116219", "percent_of_flights": 0.000890301513512573 }, { "origin_name": "LEX - LEXINGTON", "flight_count": 305, "total_distance": "109515", "percent_of_flights": 0.0008845015036525562 }, { "origin_name": "AMA - AMARILLO", "flight_count": 298, "total_distance": "115139", "percent_of_flights": 0.0008642014691424975 }, { "origin_name": "ABI - ABILENE", "flight_count": 297, "total_distance": "47075", "percent_of_flights": 0.0008613014642124892 }, { "origin_name": "TLH - TALLAHASSEE", "flight_count": 285, "total_distance": "72773", "percent_of_flights": 0.0008265014050523885 }, { "origin_name": "GPT - GULFPORT", "flight_count": 278, "total_distance": "98768", "percent_of_flights": 0.0008062013705423299 }, { "origin_name": "BTR - BATON ROUGE", "flight_count": 274, "total_distance": "102173", "percent_of_flights": 0.0007946013508222964 }, { "origin_name": "MSN - MADISON", "flight_count": 271, "total_distance": "97792", "percent_of_flights": 0.0007859013360322712 }, { "origin_name": "HNL - HONOLULU", "flight_count": 267, "total_distance": "763861", "percent_of_flights": 0.0007743013163122377 }, { "origin_name": "GGG - LONGVIEW", "flight_count": 258, "total_distance": "36120", "percent_of_flights": 0.0007482012719421624 }, { "origin_name": "DSM - DES MOINES", "flight_count": 254, "total_distance": "141238", "percent_of_flights": 0.0007366012522221287 }, { "origin_name": "GSP - GREER", "flight_count": 241, "total_distance": "105838", "percent_of_flights": 0.0006989011881320199 }, { "origin_name": "PSP - PALM SPRINGS", "flight_count": 240, "total_distance": "290300", "percent_of_flights": 0.0006960011832020114 }, { "origin_name": "SJT - SAN ANGELO", "flight_count": 223, "total_distance": "50937", "percent_of_flights": 0.0006467010993918689 }, { "origin_name": "TXK - TEXARKANA", "flight_count": 207, "total_distance": "37467", "percent_of_flights": 0.0006003010205117349 }, { "origin_name": "FSM - FORT SMITH", "flight_count": 202, "total_distance": "45854", "percent_of_flights": 0.000585800995861693 }, { "origin_name": "VLD - VALDOSTA", "flight_count": 200, "total_distance": "41600", "percent_of_flights": 0.0005800009860016763 }, { "origin_name": "PNS - PENSACOLA", "flight_count": 194, "total_distance": "90106", "percent_of_flights": 0.0005626009564216259 }, { "origin_name": "SRQ - SARASOTA/BRADENTON", "flight_count": 192, "total_distance": "152306", "percent_of_flights": 0.0005568009465616091 }, { "origin_name": "SGF - SPRINGFIELD", "flight_count": 185, "total_distance": "69245", "percent_of_flights": 0.0005365009120515505 }, { "origin_name": "HRL - HARLINGEN", "flight_count": 185, "total_distance": "51355", "percent_of_flights": 0.0005365009120515505 }, { "origin_name": "ICT - WICHITA", "flight_count": 183, "total_distance": "84296", "percent_of_flights": 0.0005307009021915338 }, { "origin_name": "CRP - CORPUS CHRISTI", "flight_count": 181, "total_distance": "42894", "percent_of_flights": 0.000524900892331517 }, { "origin_name": "LFT - LAFAYETTE", "flight_count": 176, "total_distance": "47848", "percent_of_flights": 0.0005104008676814751 }, { "origin_name": "GRK - FORT HOOD/KILLEEN", "flight_count": 161, "total_distance": "21830", "percent_of_flights": 0.00046690079373134933 }, { "origin_name": "ROA - ROANOKE", "flight_count": 158, "total_distance": "49744", "percent_of_flights": 0.0004582007789413242 }, { "origin_name": "MFE - MC ALLEN", "flight_count": 154, "total_distance": "58848", "percent_of_flights": 0.00044660075922129067 }, { "origin_name": "ILM - WILMINGTON", "flight_count": 128, "total_distance": "25270", "percent_of_flights": 0.00037120063104107277 }, { "origin_name": "MOB - MOBILE", "flight_count": 125, "total_distance": "42086", "percent_of_flights": 0.0003625006162510476 }, { "origin_name": "FAI - FAIRBANKS", "flight_count": 114, "total_distance": "81144", "percent_of_flights": 0.00033060056202095545 }, { "origin_name": "OGG - KAHULUI", "flight_count": 110, "total_distance": "236105", "percent_of_flights": 0.0003190005423009219 }, { "origin_name": "LRD - LAREDO", "flight_count": 107, "total_distance": "40185", "percent_of_flights": 0.0003103005275108968 }, { "origin_name": "MCN - MACON", "flight_count": 102, "total_distance": "8058", "percent_of_flights": 0.0002958005028608549 }, { "origin_name": "BGR - BANGOR", "flight_count": 94, "total_distance": "25044", "percent_of_flights": 0.0002726004634207878 }, { "origin_name": "BRO - BROWNSVILLE", "flight_count": 94, "total_distance": "28952", "percent_of_flights": 0.0002726004634207878 }, { "origin_name": "JAC - JACKSON", "flight_count": 86, "total_distance": "42398", "percent_of_flights": 0.00024940042398072075 }, { "origin_name": "FNT - FLINT", "flight_count": 83, "total_distance": "25384", "percent_of_flights": 0.00024070040919069562 }, { "origin_name": "FAR - FARGO", "flight_count": 83, "total_distance": "18509", "percent_of_flights": 0.00024070040919069562 }, { "origin_name": "FAY - FAYETTEVILLE", "flight_count": 83, "total_distance": "27473", "percent_of_flights": 0.00024070040919069562 }, { "origin_name": "AEX - ALEXANDRIA", "flight_count": 80, "total_distance": "18930", "percent_of_flights": 0.0002320003944006705 }, { "origin_name": "BPT - BEAUMONT/PORT ARTHUR", "flight_count": 77, "total_distance": "13914", "percent_of_flights": 0.00022330037961064533 }, { "origin_name": "STT - CHARLOTTE AMALIE", "flight_count": 76, "total_distance": "38399", "percent_of_flights": 0.00022040037468063696 }, { "origin_name": "PIE - ST PETERSBURG-CLEARWATER", "flight_count": 65, "total_distance": "77331", "percent_of_flights": 0.00018850032045054476 }, { "origin_name": "BZN - BOZEMAN", "flight_count": 62, "total_distance": "54715", "percent_of_flights": 0.00017980030566051963 }, { "origin_name": "FAT - FRESNO", "flight_count": 57, "total_distance": "74841", "percent_of_flights": 0.00016530028101047772 }, { "origin_name": "AVP - WILKES-BARRE/SCRANTON", "flight_count": 51, "total_distance": "16200", "percent_of_flights": 0.00014790025143042744 }, { "origin_name": "GRB - GREEN BAY", "flight_count": 47, "total_distance": "15120", "percent_of_flights": 0.0001363002317103939 }, { "origin_name": "EVV - EVANSVILLE", "flight_count": 42, "total_distance": "8400", "percent_of_flights": 0.000121800207060352 }, { "origin_name": "LCH - LAKE CHARLES", "flight_count": 41, "total_distance": "5207", "percent_of_flights": 0.00011890020213034362 }, { "origin_name": "MSO - MISSOULA", "flight_count": 41, "total_distance": "41533", "percent_of_flights": 0.00011890020213034362 }, { "origin_name": "LAN - LANSING", "flight_count": 40, "total_distance": "7008", "percent_of_flights": 0.00011600019720033524 }, { "origin_name": "TOL - TOLEDO", "flight_count": 40, "total_distance": "8404", "percent_of_flights": 0.00011600019720033524 }, { "origin_name": "CID - CEDAR RAPIDS", "flight_count": 40, "total_distance": "15320", "percent_of_flights": 0.00011600019720033524 }, { "origin_name": "DLH - DULUTH", "flight_count": 40, "total_distance": "5760", "percent_of_flights": 0.00011600019720033524 }, { "origin_name": "MEI - MERIDIAN", "flight_count": 37, "total_distance": "9463", "percent_of_flights": 0.0001073001824103101 }, { "origin_name": "GTR - COLUMBUS/W POINT/STARKVILL", "flight_count": 37, "total_distance": "9478", "percent_of_flights": 0.0001073001824103101 }, { "origin_name": "FSD - SIOUX FALLS", "flight_count": 35, "total_distance": "12339", "percent_of_flights": 0.00010150017255029334 }, { "origin_name": "MTJ - MONTROSE", "flight_count": 32, "total_distance": "26853", "percent_of_flights": 0.00009280015776026819 }, { "origin_name": "CAK - AKRON", "flight_count": 32, "total_distance": "8444", "percent_of_flights": 0.00009280015776026819 }, { "origin_name": "CRW - CHARLESTON", "flight_count": 32, "total_distance": "10055", "percent_of_flights": 0.00009280015776026819 }, { "origin_name": "DAB - DAYTONA BEACH", "flight_count": 31, "total_distance": "26035", "percent_of_flights": 0.00008990015283025982 }, { "origin_name": "FWA - FORT WAYNE", "flight_count": 31, "total_distance": "5332", "percent_of_flights": 0.00008990015283025982 }, { "origin_name": "SBN - SOUTH BEND", "flight_count": 25, "total_distance": "6013", "percent_of_flights": 0.00007250012325020953 }, { "origin_name": "ATW - APPLETON", "flight_count": 24, "total_distance": "11634", "percent_of_flights": 0.00006960011832020114 }, { "origin_name": "KOA - KAILUA/KONA", "flight_count": 24, "total_distance": "64144", "percent_of_flights": 0.00006960011832020114 }, { "origin_name": "EGE - EAGLE", "flight_count": 24, "total_distance": "13363", "percent_of_flights": 0.00006960011832020114 }, { "origin_name": "RAP - RAPID CITY", "flight_count": 22, "total_distance": "10780", "percent_of_flights": 0.00006380010846018439 }, { "origin_name": "ERI - ERIE", "flight_count": 21, "total_distance": "4359", "percent_of_flights": 0.000060900103530176 }, { "origin_name": "EFD - HOUSTON", "flight_count": 21, "total_distance": "588", "percent_of_flights": 0.000060900103530176 }, { "origin_name": "AZO - KALAMAZOO", "flight_count": 19, "total_distance": "4275", "percent_of_flights": 0.00005510009367015924 }, { "origin_name": "GTF - GREAT FALLS", "flight_count": 18, "total_distance": "10779", "percent_of_flights": 0.000052200088740150856 }, { "origin_name": "SBA - SANTA BARBARA", "flight_count": 18, "total_distance": "22970", "percent_of_flights": 0.000052200088740150856 }, { "origin_name": "ACY - ATLANTIC CITY", "flight_count": 18, "total_distance": "9756", "percent_of_flights": 0.000052200088740150856 }, { "origin_name": "MLI - MOLINE", "flight_count": 17, "total_distance": "5950", "percent_of_flights": 0.00004930008381014248 }, { "origin_name": "FCA - KALISPELL", "flight_count": 17, "total_distance": "11282", "percent_of_flights": 0.00004930008381014248 }, { "origin_name": "BIL - BILLINGS", "flight_count": 15, "total_distance": "9217", "percent_of_flights": 0.000043500073950125713 }, { "origin_name": "ACK - NANTUCKET", "flight_count": 15, "total_distance": "3270", "percent_of_flights": 0.000043500073950125713 }, { "origin_name": "MLU - MONROE", "flight_count": 15, "total_distance": "4240", "percent_of_flights": 0.000043500073950125713 }, { "origin_name": "LYH - LYNCHBURG", "flight_count": 14, "total_distance": "5100", "percent_of_flights": 0.00004060006902011734 }, { "origin_name": " - ", "flight_count": 14, "total_distance": "5362", "percent_of_flights": 0.00004060006902011734 }, { "origin_name": "SWF - NEWBURGH", "flight_count": 13, "total_distance": "7579", "percent_of_flights": 0.000037700064090108954 }, { "origin_name": "BGM - BINGHAMTON", "flight_count": 13, "total_distance": "6068", "percent_of_flights": 0.000037700064090108954 }, { "origin_name": "HDN - HAYDEN", "flight_count": 12, "total_distance": "9924", "percent_of_flights": 0.00003480005916010057 }, { "origin_name": "ORH - WORCESTER", "flight_count": 12, "total_distance": "1800", "percent_of_flights": 0.00003480005916010057 }, { "origin_name": "MLB - MELBOURNE", "flight_count": 10, "total_distance": "7448", "percent_of_flights": 0.00002900004930008381 }, { "origin_name": "LNK - LINCOLN", "flight_count": 9, "total_distance": "4108", "percent_of_flights": 0.000026100044370075428 }, { "origin_name": "CHO - CHARLOTTESVILLE", "flight_count": 9, "total_distance": "3078", "percent_of_flights": 0.000026100044370075428 }, { "origin_name": "BFL - BAKERSFIELD", "flight_count": 9, "total_distance": "12852", "percent_of_flights": 0.000026100044370075428 }, { "origin_name": "BQN - AGUADILLA", "flight_count": 9, "total_distance": "14184", "percent_of_flights": 0.000026100044370075428 }, { "origin_name": "CMI - CHAMPAIGN/URBANA", "flight_count": 8, "total_distance": "1640", "percent_of_flights": 0.000023200039440067048 }, { "origin_name": "GUC - GUNNISON", "flight_count": 7, "total_distance": "1590", "percent_of_flights": 0.00002030003451005867 }, { "origin_name": "ABY - ALBANY", "flight_count": 6, "total_distance": "876", "percent_of_flights": 0.000017400029580050285 }, { "origin_name": "DRO - DURANGO", "flight_count": 5, "total_distance": "3565", "percent_of_flights": 0.000014500024650041906 }, { "origin_name": "BIS - BISMARCK", "flight_count": 5, "total_distance": "1930", "percent_of_flights": 0.000014500024650041906 }, { "origin_name": "EUG - EUGENE", "flight_count": 5, "total_distance": "2255", "percent_of_flights": 0.000014500024650041906 }, { "origin_name": "HLN - HELENA", "flight_count": 5, "total_distance": "2010", "percent_of_flights": 0.000014500024650041906 }, { "origin_name": "HTS - HUNTINGTON", "flight_count": 4, "total_distance": "711", "percent_of_flights": 0.000011600019720033524 }, { "origin_name": "HVN - NEW HAVEN", "flight_count": 4, "total_distance": "2568", "percent_of_flights": 0.000011600019720033524 }, { "origin_name": "MRY - MONTEREY", "flight_count": 3, "total_distance": "231", "percent_of_flights": 0.000008700014790025143 }, { "origin_name": "PSE - PONCE", "flight_count": 3, "total_distance": "4851", "percent_of_flights": 0.000008700014790025143 }, { "origin_name": "EYW - KEY WEST", "flight_count": 3, "total_distance": "435", "percent_of_flights": 0.000008700014790025143 }, { "origin_name": "VCT - VICTORIA", "flight_count": 3, "total_distance": "369", "percent_of_flights": 0.000008700014790025143 }, { "origin_name": "STX - CHRISTIANSTED", "flight_count": 3, "total_distance": "4686", "percent_of_flights": 0.000008700014790025143 }, { "origin_name": "TVC - TRAVERSE CITY", "flight_count": 2, "total_distance": "603", "percent_of_flights": 0.000005800009860016762 }, { "origin_name": "BQK - BRUNSWICK", "flight_count": 2, "total_distance": "476", "percent_of_flights": 0.000005800009860016762 }, { "origin_name": "PHF - NEWPORT NEWS", "flight_count": 1, "total_distance": "465", "percent_of_flights": 0.000002900004930008381 }, { "origin_name": "ELM - ELMIRA/CORNING", "flight_count": 1, "total_distance": "208", "percent_of_flights": 0.000002900004930008381 }, { "origin_name": "LSE - LA CROSSE", "flight_count": 1, "total_distance": "215", "percent_of_flights": 0.000002900004930008381 }, { "origin_name": "FLO - FLORENCE", "flight_count": 1, "total_distance": "273", "percent_of_flights": 0.000002900004930008381 }, { "origin_name": "ITH - ITHACA", "flight_count": 1, "total_distance": "32", "percent_of_flights": 0.000002900004930008381 }, { "origin_name": "MFR - MEDFORD", "flight_count": 1, "total_distance": "329", "percent_of_flights": 0.000002900004930008381 } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set=1 THEN CONCAT(origin_0."code",' - ',origin_0."city") END as "origin_name__1", CASE WHEN group_set=1 THEN COUNT(1) END as "flight_count__1", CASE WHEN group_set=1 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__1", (CASE WHEN group_set=1 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=0 THEN COUNT(1) END)) OVER () as "percent_of_flights__1" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/airports.parquet' AS origin_0 ON origin_0."code"=base."origin" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1,2 ) SELECT "origin_name__1" as "origin_name", MAX(CASE WHEN group_set=1 THEN "flight_count__1" END) as "flight_count", MAX(CASE WHEN group_set=1 THEN "total_distance__1" END) as "total_distance", MAX(CASE WHEN group_set=1 THEN "percent_of_flights__1" END) as "percent_of_flights" FROM __stage0 WHERE group_set NOT IN (0) GROUP BY 1 ORDER BY 2 desc NULLS LAST
Metric by Manufacturer
run: flights -> plane_manufacturer + metrics[ { "plane_manufacturer": "BOEING", "flight_count": 183236, "total_distance": "131710785", "percent_of_flights": 0.5313853033550157 }, { "plane_manufacturer": "AIRBUS INDUSTRIE", "flight_count": 55994, "total_distance": "56029897", "percent_of_flights": 0.16238287605088927 }, { "plane_manufacturer": "MCDONNELL DOUGLAS", "flight_count": 39106, "total_distance": "35767946", "percent_of_flights": 0.11340759279290775 }, { "plane_manufacturer": "EMBRAER", "flight_count": 21799, "total_distance": "10816914", "percent_of_flights": 0.0632172074692527 }, { "plane_manufacturer": "AIRBUS", "flight_count": 7699, "total_distance": "7615122", "percent_of_flights": 0.022327137956134526 }, { "plane_manufacturer": "AEROSPATIALE/ALENIA", "flight_count": 7479, "total_distance": "1476037", "percent_of_flights": 0.02168913687153268 }, { "plane_manufacturer": "AEROSPATIALE", "flight_count": 7244, "total_distance": "1443473", "percent_of_flights": 0.021007635712980713 }, { "plane_manufacturer": "SAAB-SCANIA", "flight_count": 6395, "total_distance": "1096093", "percent_of_flights": 0.018545531527403597 }, { "plane_manufacturer": "SHORT BROS", "flight_count": 5573, "total_distance": "1059067", "percent_of_flights": 0.01616172747493671 }, { "plane_manufacturer": "CANADAIR", "flight_count": 4420, "total_distance": "1997359", "percent_of_flights": 0.012818021790637044 }, { "plane_manufacturer": "BOMBARDIER INC", "flight_count": 3901, "total_distance": "2096299", "percent_of_flights": 0.011312919231962695 }, { "plane_manufacturer": "PAIR MIKE E", "flight_count": 1635, "total_distance": "3825998", "percent_of_flights": 0.004741508060563703 }, { "plane_manufacturer": "LOCKHEED", "flight_count": 346, "total_distance": "402205", "percent_of_flights": 0.0010034017057829 } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set=1 THEN aircraft_models_0."manufacturer" END as "plane_manufacturer__1", CASE WHEN group_set=1 THEN COUNT(1) END as "flight_count__1", CASE WHEN group_set=1 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__1", (CASE WHEN group_set=1 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=0 THEN COUNT(1) END)) OVER () as "percent_of_flights__1" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/aircraft.parquet' AS aircraft_0 ON aircraft_0."tail_num"=base."tail_num" LEFT JOIN '../../documentation/data/aircraft_models.parquet' AS aircraft_models_0 ON aircraft_models_0."aircraft_model_code"=aircraft_0."aircraft_model_code" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1,2 ) SELECT "plane_manufacturer__1" as "plane_manufacturer", MAX(CASE WHEN group_set=1 THEN "flight_count__1" END) as "flight_count", MAX(CASE WHEN group_set=1 THEN "total_distance__1" END) as "total_distance", MAX(CASE WHEN group_set=1 THEN "percent_of_flights__1" END) as "percent_of_flights" FROM __stage0 WHERE group_set NOT IN (0) GROUP BY 1 ORDER BY 2 desc NULLS LAST
Nesting
One of the really powerful features of the Malloy language is nesting. We can see which carriers fly which airplanes.
run: flights -> plane_manufacturer + metrics + { nest: carrier_name + aircraft_count }
[ { "plane_manufacturer": "BOEING", "flight_count": 183236, "total_distance": "131710785", "percent_of_flights": 0.5313853033550157, "carrier_name": [ { "carrier_name": "Southwest", "aircraft_count": 8 }, { "carrier_name": "Delta", "aircraft_count": 4 }, { "carrier_name": "Northwest", "aircraft_count": 3 }, { "carrier_name": "USAir", "aircraft_count": 2 }, { "carrier_name": "United", "aircraft_count": 2 }, { "carrier_name": "America West", "aircraft_count": 1 }, { "carrier_name": "Continental", "aircraft_count": 1 }, { "carrier_name": "American", "aircraft_count": 1 }, { "carrier_name": "ATA", "aircraft_count": 1 } ] }, { "plane_manufacturer": "AIRBUS INDUSTRIE", "flight_count": 55994, "total_distance": "56029897", "percent_of_flights": 0.16238287605088927, "carrier_name": [ { "carrier_name": "United", "aircraft_count": 3 }, { "carrier_name": "USAir", "aircraft_count": 2 }, { "carrier_name": "Northwest", "aircraft_count": 2 }, { "carrier_name": "Jetblue", "aircraft_count": 1 } ] }, { "plane_manufacturer": "MCDONNELL DOUGLAS", "flight_count": 39106, "total_distance": "35767946", "percent_of_flights": 0.11340759279290775, "carrier_name": [ { "carrier_name": "American", "aircraft_count": 4 }, { "carrier_name": "Alaska", "aircraft_count": 1 } ] }, { "plane_manufacturer": "EMBRAER", "flight_count": 21799, "total_distance": "10816914", "percent_of_flights": 0.0632172074692527, "carrier_name": [ { "carrier_name": "Continental Express", "aircraft_count": 4 }, { "carrier_name": "USAir", "aircraft_count": 2 }, { "carrier_name": "Atlantic Southeast", "aircraft_count": 1 } ] }, { "plane_manufacturer": "AIRBUS", "flight_count": 7699, "total_distance": "7615122", "percent_of_flights": 0.022327137956134526, "carrier_name": [ { "carrier_name": "Northwest", "aircraft_count": 1 }, { "carrier_name": "Jetblue", "aircraft_count": 1 }, { "carrier_name": "United", "aircraft_count": 1 } ] }, { "plane_manufacturer": "AEROSPATIALE/ALENIA", "flight_count": 7479, "total_distance": "1476037", "percent_of_flights": 0.02168913687153268, "carrier_name": [ { "carrier_name": "Atlantic Southeast", "aircraft_count": 1 } ] }, { "plane_manufacturer": "AEROSPATIALE", "flight_count": 7244, "total_distance": "1443473", "percent_of_flights": 0.021007635712980713, "carrier_name": [ { "carrier_name": "Atlantic Southeast", "aircraft_count": 1 } ] }, { "plane_manufacturer": "SAAB-SCANIA", "flight_count": 6395, "total_distance": "1096093", "percent_of_flights": 0.018545531527403597, "carrier_name": [ { "carrier_name": "American Eagle", "aircraft_count": 2 } ] }, { "plane_manufacturer": "SHORT BROS", "flight_count": 5573, "total_distance": "1059067", "percent_of_flights": 0.01616172747493671, "carrier_name": [ { "carrier_name": "American Eagle", "aircraft_count": 1 } ] }, { "plane_manufacturer": "CANADAIR", "flight_count": 4420, "total_distance": "1997359", "percent_of_flights": 0.012818021790637044, "carrier_name": [ { "carrier_name": "Comair", "aircraft_count": 1 } ] }, { "plane_manufacturer": "BOMBARDIER INC", "flight_count": 3901, "total_distance": "2096299", "percent_of_flights": 0.011312919231962695, "carrier_name": [ { "carrier_name": "American Eagle", "aircraft_count": 1 } ] }, { "plane_manufacturer": "PAIR MIKE E", "flight_count": 1635, "total_distance": "3825998", "percent_of_flights": 0.004741508060563703, "carrier_name": [ { "carrier_name": "American", "aircraft_count": 1 } ] }, { "plane_manufacturer": "LOCKHEED", "flight_count": 346, "total_distance": "402205", "percent_of_flights": 0.0010034017057829, "carrier_name": [ { "carrier_name": "Delta", "aircraft_count": 1 } ] } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set IN (1,2) THEN aircraft_models_0."manufacturer" END as "plane_manufacturer__1", CASE WHEN group_set=1 THEN COUNT(1) END as "flight_count__1", CASE WHEN group_set=1 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__1", (CASE WHEN group_set=1 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=0 THEN COUNT(1) END)) OVER () as "percent_of_flights__1", CASE WHEN group_set=2 THEN carriers_0."nickname" END as "carrier_name__2", CASE WHEN group_set=2 THEN COUNT(DISTINCT aircraft_0."tail_num") END as "aircraft_count__2" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/aircraft.parquet' AS aircraft_0 ON aircraft_0."tail_num"=base."tail_num" LEFT JOIN '../../documentation/data/aircraft_models.parquet' AS aircraft_models_0 ON aircraft_models_0."aircraft_model_code"=aircraft_0."aircraft_model_code" LEFT JOIN '../../documentation/data/carriers.parquet' AS carriers_0 ON carriers_0."code"=base."carrier" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set ) as group_set GROUP BY 1,2,6 ) SELECT "plane_manufacturer__1" as "plane_manufacturer", MAX(CASE WHEN group_set=1 THEN "flight_count__1" END) as "flight_count", MAX(CASE WHEN group_set=1 THEN "total_distance__1" END) as "total_distance", MAX(CASE WHEN group_set=1 THEN "percent_of_flights__1" END) as "percent_of_flights", COALESCE(LIST({ "carrier_name": "carrier_name__2", "aircraft_count": "aircraft_count__2"} ORDER BY "aircraft_count__2" DESC NULLS LAST) FILTER (WHERE group_set=2),[]) as "carrier_name" FROM __stage0 WHERE group_set NOT IN (0) GROUP BY 1 ORDER BY 2 desc NULLS LAST
Nesting multiple queries
More than one query can be nested.
run: flights -> by_carrier + { nest: plane_manufacturer + metrics nest: origin_name + metrics + {limit: 5} }
[ { "carrier": "WN - Southwest", "flight_count": 88751, "total_distance": "54619152", "percent_of_flights": 0.2573783375431738, "plane_manufacturer": [ { "plane_manufacturer": "BOEING", "flight_count": 88751, "total_distance": "54619152", "percent_of_flights": 1 } ], "origin_name": [ { "origin_name": "PHX - PHOENIX", "flight_count": 6456, "total_distance": "4842222", "percent_of_flights": 0.07274284233417089 }, { "origin_name": "LAS - LAS VEGAS", "flight_count": 6292, "total_distance": "4196450", "percent_of_flights": 0.07089497583125824 }, { "origin_name": "BWI - BALTIMORE", "flight_count": 5471, "total_distance": "3597722", "percent_of_flights": 0.061644375838018725 }, { "origin_name": "MDW - CHICAGO", "flight_count": 4702, "total_distance": "3395623", "percent_of_flights": 0.052979684735946636 }, { "origin_name": "LAX - LOS ANGELES", "flight_count": 4282, "total_distance": "2637054", "percent_of_flights": 0.04824734369190206 } ] }, { "carrier": "US - USAir", "flight_count": 37683, "total_distance": "23721642", "percent_of_flights": 0.10928088577750582, "plane_manufacturer": [ { "plane_manufacturer": "BOEING", "flight_count": 16592, "total_distance": "7986213", "percent_of_flights": 0.440304646657644 }, { "plane_manufacturer": "AIRBUS INDUSTRIE", "flight_count": 16412, "total_distance": "12848147", "percent_of_flights": 0.43552795690364354 }, { "plane_manufacturer": "EMBRAER", "flight_count": 4679, "total_distance": "2887282", "percent_of_flights": 0.12416739643871241 } ], "origin_name": [ { "origin_name": "CLT - CHARLOTTE", "flight_count": 6427, "total_distance": "3608923", "percent_of_flights": 0.17055436138311705 }, { "origin_name": "PHL - PHILADELPHIA", "flight_count": 5779, "total_distance": "3806532", "percent_of_flights": 0.15335827826871534 }, { "origin_name": "PIT - PITTSBURGH", "flight_count": 4137, "total_distance": "2834667", "percent_of_flights": 0.10978425284611097 }, { "origin_name": "DCA - WASHINGTON", "flight_count": 2900, "total_distance": "1399695", "percent_of_flights": 0.0769577793700077 }, { "origin_name": "LGA - NEW YORK", "flight_count": 1412, "total_distance": "579791", "percent_of_flights": 0.03747047740360375 } ] }, { "carrier": "AA - American", "flight_count": 34577, "total_distance": "37684885", "percent_of_flights": 0.10027347046489979, "plane_manufacturer": [ { "plane_manufacturer": "MCDONNELL DOUGLAS", "flight_count": 30653, "total_distance": "28695495", "percent_of_flights": 0.8865141568094398 }, { "plane_manufacturer": "BOEING", "flight_count": 2289, "total_distance": "5163392", "percent_of_flights": 0.06620007519449345 }, { "plane_manufacturer": "PAIR MIKE E", "flight_count": 1635, "total_distance": "3825998", "percent_of_flights": 0.04728576799606675 } ], "origin_name": [ { "origin_name": "DFW - DALLAS-FORT WORTH", "flight_count": 8742, "total_distance": "8419987", "percent_of_flights": 0.25282702374410737 }, { "origin_name": "ORD - CHICAGO", "flight_count": 5143, "total_distance": "5207202", "percent_of_flights": 0.1487404922347225 }, { "origin_name": "LAX - LOS ANGELES", "flight_count": 1951, "total_distance": "3451194", "percent_of_flights": 0.056424791046071086 }, { "origin_name": "LGA - NEW YORK", "flight_count": 1070, "total_distance": "1055254", "percent_of_flights": 0.030945426150331145 }, { "origin_name": "MIA - MIAMI", "flight_count": 1063, "total_distance": "1301201", "percent_of_flights": 0.03074297943719814 } ] }, { "carrier": "NW - Northwest", "flight_count": 33580, "total_distance": "33376503", "percent_of_flights": 0.09738216554968143, "plane_manufacturer": [ { "plane_manufacturer": "AIRBUS INDUSTRIE", "flight_count": 15724, "total_distance": "14438258", "percent_of_flights": 0.4682549136390709 }, { "plane_manufacturer": "BOEING", "flight_count": 15274, "total_distance": "16662201", "percent_of_flights": 0.45485407980941034 }, { "plane_manufacturer": "AIRBUS", "flight_count": 2582, "total_distance": "2276044", "percent_of_flights": 0.07689100655151876 } ], "origin_name": [ { "origin_name": "MSP - MINNEAPOLIS", "flight_count": 8662, "total_distance": "8952187", "percent_of_flights": 0.2579511614055986 }, { "origin_name": "DTW - DETROIT", "flight_count": 6604, "total_distance": "5871614", "percent_of_flights": 0.19666468135795115 }, { "origin_name": "MEM - MEMPHIS", "flight_count": 1984, "total_distance": "1791631", "percent_of_flights": 0.05908278737343657 }, { "origin_name": "LAX - LOS ANGELES", "flight_count": 993, "total_distance": "1642005", "percent_of_flights": 0.029571173317450863 }, { "origin_name": "MCO - ORLANDO", "flight_count": 847, "total_distance": "868771", "percent_of_flights": 0.025223347230494342 } ] }, { "carrier": "UA - United", "flight_count": 32757, "total_distance": "38882934", "percent_of_flights": 0.09499546149228454, "plane_manufacturer": [ { "plane_manufacturer": "AIRBUS INDUSTRIE", "flight_count": 19465, "total_distance": "22868417", "percent_of_flights": 0.5942241352993254 }, { "plane_manufacturer": "BOEING", "flight_count": 8624, "total_distance": "11252652", "percent_of_flights": 0.26327197240284517 }, { "plane_manufacturer": "AIRBUS", "flight_count": 4668, "total_distance": "4761865", "percent_of_flights": 0.14250389229782948 } ], "origin_name": [ { "origin_name": "ORD - CHICAGO", "flight_count": 6802, "total_distance": "7706655", "percent_of_flights": 0.20765027322404372 }, { "origin_name": "DEN - DENVER", "flight_count": 4997, "total_distance": "5273978", "percent_of_flights": 0.1525475470891718 }, { "origin_name": "IAD - WASHINGTON", "flight_count": 2541, "total_distance": "3785080", "percent_of_flights": 0.07757120615440974 }, { "origin_name": "SFO - SAN FRANCISCO", "flight_count": 2464, "total_distance": "3222178", "percent_of_flights": 0.07522056354367006 }, { "origin_name": "LAX - LOS ANGELES", "flight_count": 2319, "total_distance": "3441449", "percent_of_flights": 0.0707940287572122 } ] }, { "carrier": "DL - Delta", "flight_count": 32130, "total_distance": "21547874", "percent_of_flights": 0.09317715840116929, "plane_manufacturer": [ { "plane_manufacturer": "BOEING", "flight_count": 31784, "total_distance": "21145669", "percent_of_flights": 0.9892312480547775 }, { "plane_manufacturer": "LOCKHEED", "flight_count": 346, "total_distance": "402205", "percent_of_flights": 0.010768751945222534 } ], "origin_name": [ { "origin_name": "ATL - ATLANTA", "flight_count": 8419, "total_distance": "5793004", "percent_of_flights": 0.2620292561469032 }, { "origin_name": "LGA - NEW YORK", "flight_count": 3295, "total_distance": "892514", "percent_of_flights": 0.10255213196389668 }, { "origin_name": "DCA - WASHINGTON", "flight_count": 1967, "total_distance": "442746", "percent_of_flights": 0.06122004357298475 }, { "origin_name": "BOS - BOSTON", "flight_count": 1721, "total_distance": "967591", "percent_of_flights": 0.05356364768129474 }, { "origin_name": "MCO - ORLANDO", "flight_count": 1640, "total_distance": "1435879", "percent_of_flights": 0.05104263927793339 } ] }, { "carrier": "RU - Continental Express", "flight_count": 16074, "total_distance": "7676766", "percent_of_flights": 0.046614679244954715, "plane_manufacturer": [ { "plane_manufacturer": "EMBRAER", "flight_count": 16074, "total_distance": "7676766", "percent_of_flights": 1 } ], "origin_name": [ { "origin_name": "CLE - CLEVELAND", "flight_count": 3007, "total_distance": "1138078", "percent_of_flights": 0.18707229065571732 }, { "origin_name": "IAH - HOUSTON", "flight_count": 2861, "total_distance": "1634764", "percent_of_flights": 0.1779892994898594 }, { "origin_name": "EWR - NEWARK", "flight_count": 2170, "total_distance": "1078257", "percent_of_flights": 0.1350006221226826 }, { "origin_name": "CLT - CHARLOTTE", "flight_count": 251, "total_distance": "149510", "percent_of_flights": 0.015615279333084485 }, { "origin_name": "DAY - DAYTON", "flight_count": 236, "total_distance": "77787", "percent_of_flights": 0.014682095309194973 } ] }, { "carrier": "MQ - American Eagle", "flight_count": 15869, "total_distance": "4251459", "percent_of_flights": 0.046020178234302996, "plane_manufacturer": [ { "plane_manufacturer": "SAAB-SCANIA", "flight_count": 6395, "total_distance": "1096093", "percent_of_flights": 0.40298695569979204 }, { "plane_manufacturer": "SHORT BROS", "flight_count": 5573, "total_distance": "1059067", "percent_of_flights": 0.3511878505261831 }, { "plane_manufacturer": "BOMBARDIER INC", "flight_count": 3901, "total_distance": "2096299", "percent_of_flights": 0.24582519377402484 } ], "origin_name": [ { "origin_name": "DFW - DALLAS-FORT WORTH", "flight_count": 6146, "total_distance": "1464011", "percent_of_flights": 0.38729598588442876 }, { "origin_name": "ORD - CHICAGO", "flight_count": 985, "total_distance": "463491", "percent_of_flights": 0.06207070388808369 }, { "origin_name": "ACT - WACO", "flight_count": 494, "total_distance": "43966", "percent_of_flights": 0.031129875858592223 }, { "origin_name": "TYR - TYLER", "flight_count": 480, "total_distance": "49440", "percent_of_flights": 0.030247652656122 }, { "origin_name": "SPS - WICHITA FALLS", "flight_count": 457, "total_distance": "51641", "percent_of_flights": 0.028798285966349486 } ] }, { "carrier": "EV - Atlantic Southeast", "flight_count": 15769, "total_distance": "3172376", "percent_of_flights": 0.04573017774130216, "plane_manufacturer": [ { "plane_manufacturer": "AEROSPATIALE/ALENIA", "flight_count": 7479, "total_distance": "1476037", "percent_of_flights": 0.4742849895364322 }, { "plane_manufacturer": "AEROSPATIALE", "flight_count": 7244, "total_distance": "1443473", "percent_of_flights": 0.4593823324243769 }, { "plane_manufacturer": "EMBRAER", "flight_count": 1046, "total_distance": "252866", "percent_of_flights": 0.06633267803919082 } ], "origin_name": [ { "origin_name": "ATL - ATLANTA", "flight_count": 7392, "total_distance": "1473933", "percent_of_flights": 0.4687678356268628 }, { "origin_name": "PFN - PANAMA CITY", "flight_count": 776, "total_distance": "191672", "percent_of_flights": 0.049210476250871965 }, { "origin_name": "AGS - AUGUSTA", "flight_count": 716, "total_distance": "102388", "percent_of_flights": 0.04540554252013444 }, { "origin_name": "CHA - CHATTANOOGA", "flight_count": 684, "total_distance": "74912", "percent_of_flights": 0.04337624453040776 }, { "origin_name": "CSG - COLUMBUS", "flight_count": 654, "total_distance": "54282", "percent_of_flights": 0.041473777665039 } ] }, { "carrier": "HP - America West", "flight_count": 9750, "total_distance": "6441707", "percent_of_flights": 0.028275048067581715, "plane_manufacturer": [ { "plane_manufacturer": "BOEING", "flight_count": 9750, "total_distance": "6441707", "percent_of_flights": 1 } ], "origin_name": [ { "origin_name": "PHX - PHOENIX", "flight_count": 3696, "total_distance": "2620479", "percent_of_flights": 0.3790769230769231 }, { "origin_name": "LAS - LAS VEGAS", "flight_count": 1221, "total_distance": "596849", "percent_of_flights": 0.12523076923076923 }, { "origin_name": "SNA - SANTA ANA", "flight_count": 420, "total_distance": "130250", "percent_of_flights": 0.043076923076923075 }, { "origin_name": "SMF - SACRAMENTO", "flight_count": 376, "total_distance": "212005", "percent_of_flights": 0.038564102564102566 }, { "origin_name": "DEN - DENVER", "flight_count": 302, "total_distance": "183451", "percent_of_flights": 0.030974358974358976 } ] }, { "carrier": "AS - Alaska", "flight_count": 8453, "total_distance": "7072451", "percent_of_flights": 0.024513741673360845, "plane_manufacturer": [ { "plane_manufacturer": "MCDONNELL DOUGLAS", "flight_count": 8453, "total_distance": "7072451", "percent_of_flights": 1 } ], "origin_name": [ { "origin_name": "SEA - SEATTLE", "flight_count": 3030, "total_distance": "2633111", "percent_of_flights": 0.35845262037146575 }, { "origin_name": "PDX - PORTLAND", "flight_count": 1038, "total_distance": "834657", "percent_of_flights": 0.12279664024606649 }, { "origin_name": "LAX - LOS ANGELES", "flight_count": 695, "total_distance": "598444", "percent_of_flights": 0.0822193304152372 }, { "origin_name": "LAS - LAS VEGAS", "flight_count": 418, "total_distance": "348156", "percent_of_flights": 0.04944989944398438 }, { "origin_name": "ANC - ANCHORAGE", "flight_count": 398, "total_distance": "492484", "percent_of_flights": 0.04708387554714303 } ] }, { "carrier": "CO - Continental", "flight_count": 7139, "total_distance": "5794572", "percent_of_flights": 0.020703135195329833, "plane_manufacturer": [ { "plane_manufacturer": "BOEING", "flight_count": 7139, "total_distance": "5794572", "percent_of_flights": 1 } ], "origin_name": [ { "origin_name": "IAH - HOUSTON", "flight_count": 1970, "total_distance": "1699834", "percent_of_flights": 0.27594901246673204 }, { "origin_name": "EWR - NEWARK", "flight_count": 1061, "total_distance": "800694", "percent_of_flights": 0.14862025493766634 }, { "origin_name": "CLE - CLEVELAND", "flight_count": 703, "total_distance": "558491", "percent_of_flights": 0.09847317551477798 }, { "origin_name": "BOS - BOSTON", "flight_count": 270, "total_distance": "188519", "percent_of_flights": 0.03782042302843536 }, { "origin_name": "ATL - ATLANTA", "flight_count": 248, "total_distance": "181074", "percent_of_flights": 0.0347387589298221 } ] }, { "carrier": "B6 - Jetblue", "flight_count": 4842, "total_distance": "6452288", "percent_of_flights": 0.01404182387110058, "plane_manufacturer": [ { "plane_manufacturer": "AIRBUS INDUSTRIE", "flight_count": 4393, "total_distance": "5875075", "percent_of_flights": 0.9072697232548533 }, { "plane_manufacturer": "AIRBUS", "flight_count": 449, "total_distance": "577213", "percent_of_flights": 0.09273027674514664 } ], "origin_name": [ { "origin_name": "JFK - NEW YORK", "flight_count": 1752, "total_distance": "2320495", "percent_of_flights": 0.3618339529120198 }, { "origin_name": "LGB - LONG BEACH", "flight_count": 471, "total_distance": "734439", "percent_of_flights": 0.09727385377942999 }, { "origin_name": "FLL - FORT LAUDERDALE", "flight_count": 375, "total_distance": "422782", "percent_of_flights": 0.07744733581164807 }, { "origin_name": "OAK - OAKLAND", "flight_count": 297, "total_distance": "493840", "percent_of_flights": 0.06133828996282528 }, { "origin_name": "MCO - ORLANDO", "flight_count": 245, "total_distance": "241126", "percent_of_flights": 0.05059892606361008 } ] }, { "carrier": "OH - Comair", "flight_count": 4420, "total_distance": "1997359", "percent_of_flights": 0.012818021790637044, "plane_manufacturer": [ { "plane_manufacturer": "CANADAIR", "flight_count": 4420, "total_distance": "1997359", "percent_of_flights": 1 } ], "origin_name": [ { "origin_name": "CVG - COVINGTON/CINCINNATI, OH", "flight_count": 1514, "total_distance": "617443", "percent_of_flights": 0.3425339366515837 }, { "origin_name": "ATL - ATLANTA", "flight_count": 239, "total_distance": "130349", "percent_of_flights": 0.05407239819004525 }, { "origin_name": "LGA - NEW YORK", "flight_count": 185, "total_distance": "116457", "percent_of_flights": 0.0418552036199095 }, { "origin_name": "JFK - NEW YORK", "flight_count": 136, "total_distance": "77482", "percent_of_flights": 0.03076923076923077 }, { "origin_name": "MCO - ORLANDO", "flight_count": 91, "total_distance": "48288", "percent_of_flights": 0.020588235294117647 } ] }, { "carrier": "TZ - ATA", "flight_count": 3033, "total_distance": "2645227", "percent_of_flights": 0.00879571495271542, "plane_manufacturer": [ { "plane_manufacturer": "BOEING", "flight_count": 3033, "total_distance": "2645227", "percent_of_flights": 1 } ], "origin_name": [ { "origin_name": "MDW - CHICAGO", "flight_count": 1309, "total_distance": "1091999", "percent_of_flights": 0.43158588855918234 }, { "origin_name": "LGA - NEW YORK", "flight_count": 210, "total_distance": "151188", "percent_of_flights": 0.06923837784371908 }, { "origin_name": "IND - INDIANAPOLIS", "flight_count": 200, "total_distance": "193945", "percent_of_flights": 0.06594131223211341 }, { "origin_name": "MSP - MINNEAPOLIS", "flight_count": 163, "total_distance": "56887", "percent_of_flights": 0.053742169469172436 }, { "origin_name": "DFW - DALLAS-FORT WORTH", "flight_count": 132, "total_distance": "104924", "percent_of_flights": 0.043521266073194856 } ] } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set IN (1,2,3) THEN CONCAT(base."carrier",' - ',(carriers_0."nickname")) END as "carrier__1", CASE WHEN group_set=1 THEN COUNT(1) END as "flight_count__1", CASE WHEN group_set=1 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__1", (CASE WHEN group_set=1 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=0 THEN COUNT(1) END)) OVER () as "percent_of_flights__1", CASE WHEN group_set=2 THEN aircraft_models_0."manufacturer" END as "plane_manufacturer__2", CASE WHEN group_set=2 THEN COUNT(1) END as "flight_count__2", CASE WHEN group_set=2 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__2", (CASE WHEN group_set=2 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=1 THEN COUNT(1) END)) OVER (PARTITION BY CASE WHEN group_set IN (1,2,3) THEN CONCAT(base."carrier",' - ',(carriers_0."nickname")) END) as "percent_of_flights__2", CASE WHEN group_set=3 THEN CONCAT(origin_0."code",' - ',origin_0."city") END as "origin_name__3", CASE WHEN group_set=3 THEN COUNT(1) END as "flight_count__3", CASE WHEN group_set=3 THEN COALESCE(SUM(base."distance"),0) END as "total_distance__3", (CASE WHEN group_set=3 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=1 THEN COUNT(1) END)) OVER (PARTITION BY CASE WHEN group_set IN (1,2,3) THEN CONCAT(base."carrier",' - ',(carriers_0."nickname")) END) as "percent_of_flights__3" FROM '../../documentation/data/flights.parquet' as base LEFT JOIN '../../documentation/data/aircraft.parquet' AS aircraft_0 ON aircraft_0."tail_num"=base."tail_num" LEFT JOIN '../../documentation/data/aircraft_models.parquet' AS aircraft_models_0 ON aircraft_models_0."aircraft_model_code"=aircraft_0."aircraft_model_code" LEFT JOIN '../../documentation/data/airports.parquet' AS origin_0 ON origin_0."code"=base."origin" LEFT JOIN '../../documentation/data/carriers.parquet' AS carriers_0 ON carriers_0."code"=base."carrier" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,3,1)) as group_set ) as group_set GROUP BY 1,2,6,10 ) , __stage1 AS ( SELECT *, CASE WHEN GROUP_SET=3 THEN ROW_NUMBER() OVER (PARTITION BY CAST("carrier__1" as VARCHAR), group_set ORDER BY "flight_count__3" desc) END as __row_number__3 FROM __stage0 ) , __stage2 AS ( SELECT * FROM __stage1 WHERE NOT ((GROUP_SET = 3 AND __row_number__3 > 5)) ) SELECT "carrier__1" as "carrier", MAX(CASE WHEN group_set=1 THEN "flight_count__1" END) as "flight_count", MAX(CASE WHEN group_set=1 THEN "total_distance__1" END) as "total_distance", MAX(CASE WHEN group_set=1 THEN "percent_of_flights__1" END) as "percent_of_flights", COALESCE(LIST({ "plane_manufacturer": "plane_manufacturer__2", "flight_count": "flight_count__2", "total_distance": "total_distance__2", "percent_of_flights": "percent_of_flights__2"} ORDER BY "flight_count__2" DESC NULLS LAST) FILTER (WHERE group_set=2),[]) as "plane_manufacturer", COALESCE(LIST({ "origin_name": "origin_name__3", "flight_count": "flight_count__3", "total_distance": "total_distance__3", "percent_of_flights": "percent_of_flights__3"} ORDER BY "flight_count__3" DESC NULLS LAST) FILTER (WHERE group_set=3),[]) as "origin_name" FROM __stage2 WHERE group_set NOT IN (0) GROUP BY 1 ORDER BY 2 desc NULLS LAST
And there you have it
Writing queries with a pre-built model is really powerful. You can easily explore a complex dataset in a simple, composable way. The output of these queries can be used in lots of different ways, from Data Science to transforming data for consumption in other systems.
Pre-built semantic models take much of the pain out of working with data.