All PostsA 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.
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=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 __stage0 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)[1:6],[]) 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 __stage1 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": "GNV - GAINESVILLE", "flight_count": 470 }, { "origin_name": "GSO - GREENSBORO", "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": "DAY - DAYTON", "flight_count": 416 }, { "origin_name": "ILE - KILLEEN", "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": "HRL - HARLINGEN", "flight_count": 185 }, { "origin_name": "SGF - SPRINGFIELD", "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": "BRO - BROWNSVILLE", "flight_count": 94 }, { "origin_name": "BGR - BANGOR", "flight_count": 94 }, { "origin_name": "JAC - JACKSON", "flight_count": 86 }, { "origin_name": "FAY - FAYETTEVILLE", "flight_count": 83 }, { "origin_name": "FNT - FLINT", "flight_count": 83 }, { "origin_name": "FAR - FARGO", "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": "TOL - TOLEDO", "flight_count": 40 }, { "origin_name": "LAN - LANSING", "flight_count": 40 }, { "origin_name": "CID - CEDAR RAPIDS", "flight_count": 40 }, { "origin_name": "DLH - DULUTH", "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": "MTJ - MONTROSE", "flight_count": 32 }, { "origin_name": "CAK - AKRON", "flight_count": 32 }, { "origin_name": "CRW - CHARLESTON", "flight_count": 32 }, { "origin_name": "FWA - FORT WAYNE", "flight_count": 31 }, { "origin_name": "DAB - DAYTONA BEACH", "flight_count": 31 }, { "origin_name": "SBN - SOUTH BEND", "flight_count": 25 }, { "origin_name": "ATW - APPLETON", "flight_count": 24 }, { "origin_name": "KOA - KAILUA/KONA", "flight_count": 24 }, { "origin_name": "EGE - EAGLE", "flight_count": 24 }, { "origin_name": "RAP - RAPID CITY", "flight_count": 22 }, { "origin_name": "ERI - ERIE", "flight_count": 21 }, { "origin_name": "EFD - HOUSTON", "flight_count": 21 }, { "origin_name": "AZO - KALAMAZOO", "flight_count": 19 }, { "origin_name": "ACY - ATLANTIC CITY", "flight_count": 18 }, { "origin_name": "SBA - SANTA BARBARA", "flight_count": 18 }, { "origin_name": "GTF - GREAT FALLS", "flight_count": 18 }, { "origin_name": "MLI - MOLINE", "flight_count": 17 }, { "origin_name": "FCA - KALISPELL", "flight_count": 17 }, { "origin_name": "ACK - NANTUCKET", "flight_count": 15 }, { "origin_name": "BIL - BILLINGS", "flight_count": 15 }, { "origin_name": "MLU - MONROE", "flight_count": 15 }, { "origin_name": " - ", "flight_count": 14 }, { "origin_name": "LYH - LYNCHBURG", "flight_count": 14 }, { "origin_name": "BGM - BINGHAMTON", "flight_count": 13 }, { "origin_name": "SWF - NEWBURGH", "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": "CHO - CHARLOTTESVILLE", "flight_count": 9 }, { "origin_name": "BQN - AGUADILLA", "flight_count": 9 }, { "origin_name": "LNK - LINCOLN", "flight_count": 9 }, { "origin_name": "BFL - BAKERSFIELD", "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": "EUG - EUGENE", "flight_count": 5 }, { "origin_name": "DRO - DURANGO", "flight_count": 5 }, { "origin_name": "HLN - HELENA", "flight_count": 5 }, { "origin_name": "BIS - BISMARCK", "flight_count": 5 }, { "origin_name": "HTS - HUNTINGTON", "flight_count": 4 }, { "origin_name": "HVN - NEW HAVEN", "flight_count": 4 }, { "origin_name": "MRY - MONTEREY", "flight_count": 3 }, { "origin_name": "STX - CHRISTIANSTED", "flight_count": 3 }, { "origin_name": "EYW - KEY WEST", "flight_count": 3 }, { "origin_name": "PSE - PONCE", "flight_count": 3 }, { "origin_name": "VCT - VICTORIA", "flight_count": 3 }, { "origin_name": "BQK - BRUNSWICK", "flight_count": 2 }, { "origin_name": "TVC - TRAVERSE CITY", "flight_count": 2 }, { "origin_name": "MFR - MEDFORD", "flight_count": 1 }, { "origin_name": "FLO - FLORENCE", "flight_count": 1 }, { "origin_name": "LSE - LA CROSSE", "flight_count": 1 }, { "origin_name": "ELM - ELMIRA/CORNING", "flight_count": 1 }, { "origin_name": "PHF - NEWPORT NEWS", "flight_count": 1 }, { "origin_name": "ITH - ITHACA", "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": "CHO - CHARLOTTESVILLE", "seats_for_sale": 495, "flight_count": 9 }, { "origin_name": "BFL - BAKERSFIELD", "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": "RU - Continental Express", "origin_count": 126, "flight_count": 16074 }, { "carrier": "OH - Comair", "origin_count": 120, "flight_count": 4420 }, { "carrier": "DL - Delta", "origin_count": 97, "flight_count": 32130 }, { "carrier": "AA - American", "origin_count": 89, "flight_count": 34577 }, { "carrier": "UA - United", "origin_count": 89, "flight_count": 32757 }, { "carrier": "NW - Northwest", "origin_count": 88, "flight_count": 33580 }, { "carrier": "US - USAir", "origin_count": 82, "flight_count": 37683 }, { "carrier": "CO - Continental", "origin_count": 78, "flight_count": 7139 }, { "carrier": "MQ - American Eagle", "origin_count": 77, "flight_count": 15869 }, { "carrier": "WN - Southwest", "origin_count": 63, "flight_count": 88751 }, { "carrier": "EV - Atlantic Southeast", "origin_count": 59, "flight_count": 15769 }, { "carrier": "HP - America West", "origin_count": 41, "flight_count": 9750 }, { "carrier": "B6 - Jetblue", "origin_count": 32, "flight_count": 4842 }, { "carrier": "TZ - ATA", "origin_count": 25, "flight_count": 3033 }, { "carrier": "AS - Alaska", "origin_count": 22, "flight_count": 8453 } ]
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 2 desc 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": 84, "carrier_name": "Continental" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 403, "carrier_name": "Delta" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 1159, "carrier_name": "Southwest" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 486, "carrier_name": "American" }, { "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": 432, "carrier_name": "Northwest" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 108, "carrier_name": "Alaska" }, { "flight_month": "2001-12-01T00:00:00.000Z", "flight_count": 418, "carrier_name": "United" }, { "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": 634, "carrier_name": "USAir" }, { "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": 499, "carrier_name": "USAir" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 399, "carrier_name": "United" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 1224, "carrier_name": "Southwest" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 462, "carrier_name": "American" }, { "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": 113, "carrier_name": "Alaska" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 317, "carrier_name": "Northwest" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 101, "carrier_name": "Continental" }, { "flight_month": "2001-11-01T00:00:00.000Z", "flight_count": 440, "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": 456, "carrier_name": "USAir" }, { "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": 110, "carrier_name": "Continental" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 1309, "carrier_name": "Southwest" }, { "flight_month": "2001-10-01T00:00:00.000Z", "flight_count": 455, "carrier_name": "American" }, { "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": 290, "carrier_name": "Northwest" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 1150, "carrier_name": "Southwest" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 347, "carrier_name": "American" }, { "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": 450, "carrier_name": "Delta" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 76, "carrier_name": "Continental" }, { "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": 449, "carrier_name": "USAir" }, { "flight_month": "2001-09-01T00:00:00.000Z", "flight_count": 296, "carrier_name": "United" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 554, "carrier_name": "Delta" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 116, "carrier_name": "Continental" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 1318, "carrier_name": "Southwest" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 557, "carrier_name": "American" }, { "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": 516, "carrier_name": "Northwest" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 133, "carrier_name": "Alaska" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 363, "carrier_name": "United" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 554, "carrier_name": "USAir" }, { "flight_month": "2001-08-01T00:00:00.000Z", "flight_count": 343, "carrier_name": "American Eagle" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 272, "carrier_name": "United" }, { "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": 583, "carrier_name": "USAir" }, { "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": 1299, "carrier_name": "Southwest" }, { "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": 143, "carrier_name": "Alaska" }, { "flight_month": "2001-07-01T00:00:00.000Z", "flight_count": 491, "carrier_name": "Northwest" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 485, "carrier_name": "Delta" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 110, "carrier_name": "Continental" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 1254, "carrier_name": "Southwest" }, { "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": 512, "carrier_name": "Northwest" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 133, "carrier_name": "Alaska" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 338, "carrier_name": "United" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 309, "carrier_name": "American Eagle" }, { "flight_month": "2001-06-01T00:00:00.000Z", "flight_count": 607, "carrier_name": "USAir" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 535, "carrier_name": "Delta" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 124, "carrier_name": "Continental" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 1216, "carrier_name": "Southwest" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 466, "carrier_name": "American" }, { "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": 491, "carrier_name": "Northwest" }, { "flight_month": "2001-05-01T00:00:00.000Z", "flight_count": 312, "carrier_name": "United" }, { "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-04-01T00:00:00.000Z", "flight_count": 1218, "carrier_name": "Southwest" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 523, "carrier_name": "American" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 478, "carrier_name": "Northwest" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 120, "carrier_name": "Alaska" }, { "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": 553, "carrier_name": "Delta" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 112, "carrier_name": "Continental" }, { "flight_month": "2001-04-01T00:00:00.000Z", "flight_count": 641, "carrier_name": "USAir" }, { "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": 277, "carrier_name": "United" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 589, "carrier_name": "Delta" }, { "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": 747, "carrier_name": "Southwest" }, { "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": 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": 318, "carrier_name": "United" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 589, "carrier_name": "USAir" }, { "flight_month": "2001-03-01T00:00:00.000Z", "flight_count": 332, "carrier_name": "American Eagle" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 387, "carrier_name": "Delta" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 92, "carrier_name": "Continental" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 1212, "carrier_name": "Southwest" }, { "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": 464, "carrier_name": "Northwest" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 443, "carrier_name": "American" }, { "flight_month": "2001-02-01T00:00:00.000Z", "flight_count": 138, "carrier_name": "Alaska" }, { "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-01-01T00:00:00.000Z", "flight_count": 201, "carrier_name": "United" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 656, "carrier_name": "USAir" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 437, "carrier_name": "Delta" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 134, "carrier_name": "Continental" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 1315, "carrier_name": "Southwest" }, { "flight_month": "2001-01-01T00:00:00.000Z", "flight_count": 533, "carrier_name": "American" }, { "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": 118, "carrier_name": "Alaska" } ]
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": "WN - Southwest", "flight_count": 88751, "total_distance": 54619152, "percent_of_flights": 0.2573783375431738 }, { "carrier": "US - USAir", "flight_count": 37683, "total_distance": 23721642, "percent_of_flights": 0.10928088577750582 }, { "carrier": "AA - American", "flight_count": 34577, "total_distance": 37684885, "percent_of_flights": 0.10027347046489979 }, { "carrier": "NW - Northwest", "flight_count": 33580, "total_distance": 33376503, "percent_of_flights": 0.09738216554968143 }, { "carrier": "UA - United", "flight_count": 32757, "total_distance": 38882934, "percent_of_flights": 0.09499546149228454 }, { "carrier": "DL - Delta", "flight_count": 32130, "total_distance": 21547874, "percent_of_flights": 0.09317715840116929 }, { "carrier": "RU - Continental Express", "flight_count": 16074, "total_distance": 7676766, "percent_of_flights": 0.046614679244954715 }, { "carrier": "MQ - American Eagle", "flight_count": 15869, "total_distance": 4251459, "percent_of_flights": 0.046020178234302996 }, { "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": "AS - Alaska", "flight_count": 8453, "total_distance": 7072451, "percent_of_flights": 0.024513741673360845 }, { "carrier": "CO - Continental", "flight_count": 7139, "total_distance": 5794572, "percent_of_flights": 0.020703135195329833 }, { "carrier": "B6 - Jetblue", "flight_count": 4842, "total_distance": 6452288, "percent_of_flights": 0.01404182387110058 }, { "carrier": "OH - Comair", "flight_count": 4420, "total_distance": 1997359, "percent_of_flights": 0.012818021790637044 }, { "carrier": "TZ - ATA", "flight_count": 3033, "total_distance": 2645227, "percent_of_flights": 0.00879571495271542 } ]
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 2 desc 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": "OMA - OMAHA", "flight_count": 946, "total_distance": 630528, "percent_of_flights": 0.0027434046637879283 }, { "origin_name": "RIC - RICHMOND", "flight_count": 946, "total_distance": 378039, "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": "GNV - GAINESVILLE", "flight_count": 470, "total_distance": 140922, "percent_of_flights": 0.0013630023171039391 }, { "origin_name": "GSO - GREENSBORO", "flight_count": 470, "total_distance": 174414, "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": "DAY - DAYTON", "flight_count": 416, "total_distance": 153036, "percent_of_flights": 0.0012064020508834865 }, { "origin_name": "ILE - KILLEEN", "flight_count": 416, "total_distance": 54109, "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": "HRL - HARLINGEN", "flight_count": 185, "total_distance": 51355, "percent_of_flights": 0.0005365009120515505 }, { "origin_name": "SGF - SPRINGFIELD", "flight_count": 185, "total_distance": 69245, "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": "BRO - BROWNSVILLE", "flight_count": 94, "total_distance": 28952, "percent_of_flights": 0.0002726004634207878 }, { "origin_name": "BGR - BANGOR", "flight_count": 94, "total_distance": 25044, "percent_of_flights": 0.0002726004634207878 }, { "origin_name": "JAC - JACKSON", "flight_count": 86, "total_distance": 42398, "percent_of_flights": 0.00024940042398072075 }, { "origin_name": "FAY - FAYETTEVILLE", "flight_count": 83, "total_distance": 27473, "percent_of_flights": 0.00024070040919069562 }, { "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": "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": "MSO - MISSOULA", "flight_count": 41, "total_distance": 41533, "percent_of_flights": 0.00011890020213034362 }, { "origin_name": "LCH - LAKE CHARLES", "flight_count": 41, "total_distance": 5207, "percent_of_flights": 0.00011890020213034362 }, { "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": "LAN - LANSING", "flight_count": 40, "total_distance": 7008, "percent_of_flights": 0.00011600019720033524 }, { "origin_name": "GTR - COLUMBUS/W POINT/STARKVILL", "flight_count": 37, "total_distance": 9478, "percent_of_flights": 0.0001073001824103101 }, { "origin_name": "MEI - MERIDIAN", "flight_count": 37, "total_distance": 9463, "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": "FWA - FORT WAYNE", "flight_count": 31, "total_distance": 5332, "percent_of_flights": 0.00008990015283025982 }, { "origin_name": "DAB - DAYTONA BEACH", "flight_count": 31, "total_distance": 26035, "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": "EFD - HOUSTON", "flight_count": 21, "total_distance": 588, "percent_of_flights": 0.000060900103530176 }, { "origin_name": "ERI - ERIE", "flight_count": 21, "total_distance": 4359, "percent_of_flights": 0.000060900103530176 }, { "origin_name": "AZO - KALAMAZOO", "flight_count": 19, "total_distance": 4275, "percent_of_flights": 0.00005510009367015924 }, { "origin_name": "ACY - ATLANTIC CITY", "flight_count": 18, "total_distance": 9756, "percent_of_flights": 0.000052200088740150856 }, { "origin_name": "SBA - SANTA BARBARA", "flight_count": 18, "total_distance": 22970, "percent_of_flights": 0.000052200088740150856 }, { "origin_name": "GTF - GREAT FALLS", "flight_count": 18, "total_distance": 10779, "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": "ACK - NANTUCKET", "flight_count": 15, "total_distance": 3270, "percent_of_flights": 0.000043500073950125713 }, { "origin_name": "BIL - BILLINGS", "flight_count": 15, "total_distance": 9217, "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": "BGM - BINGHAMTON", "flight_count": 13, "total_distance": 6068, "percent_of_flights": 0.000037700064090108954 }, { "origin_name": "SWF - NEWBURGH", "flight_count": 13, "total_distance": 7579, "percent_of_flights": 0.000037700064090108954 }, { "origin_name": "ORH - WORCESTER", "flight_count": 12, "total_distance": 1800, "percent_of_flights": 0.00003480005916010057 }, { "origin_name": "HDN - HAYDEN", "flight_count": 12, "total_distance": 9924, "percent_of_flights": 0.00003480005916010057 }, { "origin_name": "MLB - MELBOURNE", "flight_count": 10, "total_distance": 7448, "percent_of_flights": 0.00002900004930008381 }, { "origin_name": "CHO - CHARLOTTESVILLE", "flight_count": 9, "total_distance": 3078, "percent_of_flights": 0.000026100044370075428 }, { "origin_name": "BQN - AGUADILLA", "flight_count": 9, "total_distance": 14184, "percent_of_flights": 0.000026100044370075428 }, { "origin_name": "LNK - LINCOLN", "flight_count": 9, "total_distance": 4108, "percent_of_flights": 0.000026100044370075428 }, { "origin_name": "BFL - BAKERSFIELD", "flight_count": 9, "total_distance": 12852, "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": "EUG - EUGENE", "flight_count": 5, "total_distance": 2255, "percent_of_flights": 0.000014500024650041906 }, { "origin_name": "DRO - DURANGO", "flight_count": 5, "total_distance": 3565, "percent_of_flights": 0.000014500024650041906 }, { "origin_name": "HLN - HELENA", "flight_count": 5, "total_distance": 2010, "percent_of_flights": 0.000014500024650041906 }, { "origin_name": "BIS - BISMARCK", "flight_count": 5, "total_distance": 1930, "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": "STX - CHRISTIANSTED", "flight_count": 3, "total_distance": 4686, "percent_of_flights": 0.000008700014790025143 }, { "origin_name": "EYW - KEY WEST", "flight_count": 3, "total_distance": 435, "percent_of_flights": 0.000008700014790025143 }, { "origin_name": "PSE - PONCE", "flight_count": 3, "total_distance": 4851, "percent_of_flights": 0.000008700014790025143 }, { "origin_name": "VCT - VICTORIA", "flight_count": 3, "total_distance": 369, "percent_of_flights": 0.000008700014790025143 }, { "origin_name": "BQK - BRUNSWICK", "flight_count": 2, "total_distance": 476, "percent_of_flights": 0.000005800009860016762 }, { "origin_name": "TVC - TRAVERSE CITY", "flight_count": 2, "total_distance": 603, "percent_of_flights": 0.000005800009860016762 }, { "origin_name": "MFR - MEDFORD", "flight_count": 1, "total_distance": 329, "percent_of_flights": 0.000002900004930008381 }, { "origin_name": "LSE - LA CROSSE", "flight_count": 1, "total_distance": 215, "percent_of_flights": 0.000002900004930008381 }, { "origin_name": "ELM - ELMIRA/CORNING", "flight_count": 1, "total_distance": 208, "percent_of_flights": 0.000002900004930008381 }, { "origin_name": "FLO - FLORENCE", "flight_count": 1, "total_distance": 273, "percent_of_flights": 0.000002900004930008381 }, { "origin_name": "PHF - NEWPORT NEWS", "flight_count": 1, "total_distance": 465, "percent_of_flights": 0.000002900004930008381 }, { "origin_name": "ITH - ITHACA", "flight_count": 1, "total_distance": 32, "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": "American", "aircraft_count": 1 }, { "carrier_name": "Continental", "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": "Northwest", "aircraft_count": 2 }, { "carrier_name": "USAir", "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/carriers.parquet' AS carriers_0 ON carriers_0."code"=base."carrier" 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" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,3,1)) as group_set ) as group_set GROUP BY 1,2,6,10 ) 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)[1:5],[]) as "origin_name" FROM __stage0 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.
