Malloy Documentation
search

Malloy can create simple bar charts and bar charts with series breakdowns, either stacked or grouped.

The examples below all use the following semantic model.

document
source: flights is duckdb.table('../data/flights.parquet') extend {
  join_one: carriers is duckdb.table('../data/carriers.parquet') 
    on carrier = carriers.code
  measure: 
    flight_count is count()
    to_sfo_flight_count is flight_count { where: destination = 'SFO' }
    to_oak_flight_count is flight_count { where: destination = 'OAK' }
    from_sfo_flight_count is flight_count { where: origin = 'SFO' }
    from_oak_flight_count is flight_count { where: origin = 'OAK' }

    aircraft_count is count(tail_num)
}

Simple Bar Chart

Basic bar charts use the first dimension as the X-axis, and the first aggregate as the Y-axis:

document
# bar_chart
run: flights -> {
  group_by: carriers.nickname
  aggregate: flight_count
}
QUERY RESULTS
[
  {
    "nickname": "Southwest",
    "flight_count": 88751
  },
  {
    "nickname": "USAir",
    "flight_count": 37683
  },
  {
    "nickname": "American",
    "flight_count": 34577
  },
  {
    "nickname": "Northwest",
    "flight_count": 33580
  },
  {
    "nickname": "United",
    "flight_count": 32757
  },
  {
    "nickname": "Delta",
    "flight_count": 32130
  },
  {
    "nickname": "Continental Express",
    "flight_count": 16074
  },
  {
    "nickname": "American Eagle",
    "flight_count": 15869
  },
  {
    "nickname": "Atlantic Southeast",
    "flight_count": 15769
  },
  {
    "nickname": "America West",
    "flight_count": 9750
  },
  {
    "nickname": "Alaska",
    "flight_count": 8453
  },
  {
    "nickname": "Continental",
    "flight_count": 7139
  },
  {
    "nickname": "Jetblue",
    "flight_count": 4842
  },
  {
    "nickname": "Comair",
    "flight_count": 4420
  },
  {
    "nickname": "ATA",
    "flight_count": 3033
  }
]
SELECT 
   carriers_0."nickname" as "nickname",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Bar Chart with Breakdown

Adding a second dimension to the query in the group_by: clause creates a bar chart with a breakdown along that second dimension:

document
# bar_chart
run: flights -> {
  where: destination ? 'SFO' | 'OAK' | 'SJC'
  group_by: carriers.nickname, destination
  aggregate: flight_count
}
QUERY RESULTS
[
  {
    "nickname": "Southwest",
    "destination": "OAK",
    "flight_count": 3967
  },
  {
    "nickname": "United",
    "destination": "SFO",
    "flight_count": 2432
  },
  {
    "nickname": "Southwest",
    "destination": "SJC",
    "flight_count": 2143
  },
  {
    "nickname": "American",
    "destination": "SFO",
    "flight_count": 686
  },
  {
    "nickname": "American",
    "destination": "SJC",
    "flight_count": 558
  },
  {
    "nickname": "Northwest",
    "destination": "SFO",
    "flight_count": 520
  },
  {
    "nickname": "Alaska",
    "destination": "SJC",
    "flight_count": 367
  },
  {
    "nickname": "United",
    "destination": "SJC",
    "flight_count": 318
  },
  {
    "nickname": "Jetblue",
    "destination": "OAK",
    "flight_count": 297
  },
  {
    "nickname": "United",
    "destination": "OAK",
    "flight_count": 249
  },
  {
    "nickname": "Alaska",
    "destination": "OAK",
    "flight_count": 248
  },
  {
    "nickname": "Alaska",
    "destination": "SFO",
    "flight_count": 223
  },
  {
    "nickname": "America West",
    "destination": "SFO",
    "flight_count": 209
  },
  {
    "nickname": "America West",
    "destination": "SJC",
    "flight_count": 180
  },
  {
    "nickname": "Northwest",
    "destination": "SJC",
    "flight_count": 168
  },
  {
    "nickname": "America West",
    "destination": "OAK",
    "flight_count": 162
  },
  {
    "nickname": "USAir",
    "destination": "SFO",
    "flight_count": 156
  },
  {
    "nickname": "American",
    "destination": "OAK",
    "flight_count": 141
  },
  {
    "nickname": "Delta",
    "destination": "SFO",
    "flight_count": 132
  },
  {
    "nickname": "Southwest",
    "destination": "SFO",
    "flight_count": 86
  },
  {
    "nickname": "ATA",
    "destination": "SFO",
    "flight_count": 37
  },
  {
    "nickname": "Continental",
    "destination": "SJC",
    "flight_count": 35
  },
  {
    "nickname": "Continental",
    "destination": "SFO",
    "flight_count": 21
  },
  {
    "nickname": "Jetblue",
    "destination": "SJC",
    "flight_count": 21
  },
  {
    "nickname": "Delta",
    "destination": "SJC",
    "flight_count": 19
  },
  {
    "nickname": "Delta",
    "destination": "OAK",
    "flight_count": 10
  },
  {
    "nickname": "ATA",
    "destination": "SJC",
    "flight_count": 9
  },
  {
    "nickname": "Continental",
    "destination": "OAK",
    "flight_count": 8
  }
]
SELECT 
   carriers_0."nickname" as "nickname",
   base."destination" as "destination",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
WHERE base."destination" IN ('SFO','OAK','SJC')
GROUP BY 1,2
ORDER BY 3 desc NULLS LAST
document
# bar_chart
run: flights -> {
  where: destination ? 'SFO' | 'OAK' | 'SJC'
  group_by: destination, carriers.nickname
  aggregate: flight_count
}
QUERY RESULTS
[
  {
    "destination": "OAK",
    "nickname": "Southwest",
    "flight_count": 3967
  },
  {
    "destination": "SFO",
    "nickname": "United",
    "flight_count": 2432
  },
  {
    "destination": "SJC",
    "nickname": "Southwest",
    "flight_count": 2143
  },
  {
    "destination": "SFO",
    "nickname": "American",
    "flight_count": 686
  },
  {
    "destination": "SJC",
    "nickname": "American",
    "flight_count": 558
  },
  {
    "destination": "SFO",
    "nickname": "Northwest",
    "flight_count": 520
  },
  {
    "destination": "SJC",
    "nickname": "Alaska",
    "flight_count": 367
  },
  {
    "destination": "SJC",
    "nickname": "United",
    "flight_count": 318
  },
  {
    "destination": "OAK",
    "nickname": "Jetblue",
    "flight_count": 297
  },
  {
    "destination": "OAK",
    "nickname": "United",
    "flight_count": 249
  },
  {
    "destination": "OAK",
    "nickname": "Alaska",
    "flight_count": 248
  },
  {
    "destination": "SFO",
    "nickname": "Alaska",
    "flight_count": 223
  },
  {
    "destination": "SFO",
    "nickname": "America West",
    "flight_count": 209
  },
  {
    "destination": "SJC",
    "nickname": "America West",
    "flight_count": 180
  },
  {
    "destination": "SJC",
    "nickname": "Northwest",
    "flight_count": 168
  },
  {
    "destination": "OAK",
    "nickname": "America West",
    "flight_count": 162
  },
  {
    "destination": "SFO",
    "nickname": "USAir",
    "flight_count": 156
  },
  {
    "destination": "OAK",
    "nickname": "American",
    "flight_count": 141
  },
  {
    "destination": "SFO",
    "nickname": "Delta",
    "flight_count": 132
  },
  {
    "destination": "SFO",
    "nickname": "Southwest",
    "flight_count": 86
  },
  {
    "destination": "SFO",
    "nickname": "ATA",
    "flight_count": 37
  },
  {
    "destination": "SJC",
    "nickname": "Continental",
    "flight_count": 35
  },
  {
    "destination": "SJC",
    "nickname": "Jetblue",
    "flight_count": 21
  },
  {
    "destination": "SFO",
    "nickname": "Continental",
    "flight_count": 21
  },
  {
    "destination": "SJC",
    "nickname": "Delta",
    "flight_count": 19
  },
  {
    "destination": "OAK",
    "nickname": "Delta",
    "flight_count": 10
  },
  {
    "destination": "SJC",
    "nickname": "ATA",
    "flight_count": 9
  },
  {
    "destination": "OAK",
    "nickname": "Continental",
    "flight_count": 8
  }
]
SELECT 
   base."destination" as "destination",
   carriers_0."nickname" as "nickname",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
WHERE base."destination" IN ('SFO','OAK','SJC')
GROUP BY 1,2
ORDER BY 3 desc NULLS LAST

To stack bars instead of grouping them, add the stack tag:

document
# bar_chart.stack
run: flights -> {
  where: destination ? 'SFO' | 'OAK' | 'SJC'
  group_by: carriers.nickname, destination
  aggregate: flight_count
}
QUERY RESULTS
[
  {
    "nickname": "Southwest",
    "destination": "OAK",
    "flight_count": 3967
  },
  {
    "nickname": "United",
    "destination": "SFO",
    "flight_count": 2432
  },
  {
    "nickname": "Southwest",
    "destination": "SJC",
    "flight_count": 2143
  },
  {
    "nickname": "American",
    "destination": "SFO",
    "flight_count": 686
  },
  {
    "nickname": "American",
    "destination": "SJC",
    "flight_count": 558
  },
  {
    "nickname": "Northwest",
    "destination": "SFO",
    "flight_count": 520
  },
  {
    "nickname": "Alaska",
    "destination": "SJC",
    "flight_count": 367
  },
  {
    "nickname": "United",
    "destination": "SJC",
    "flight_count": 318
  },
  {
    "nickname": "Jetblue",
    "destination": "OAK",
    "flight_count": 297
  },
  {
    "nickname": "United",
    "destination": "OAK",
    "flight_count": 249
  },
  {
    "nickname": "Alaska",
    "destination": "OAK",
    "flight_count": 248
  },
  {
    "nickname": "Alaska",
    "destination": "SFO",
    "flight_count": 223
  },
  {
    "nickname": "America West",
    "destination": "SFO",
    "flight_count": 209
  },
  {
    "nickname": "America West",
    "destination": "SJC",
    "flight_count": 180
  },
  {
    "nickname": "Northwest",
    "destination": "SJC",
    "flight_count": 168
  },
  {
    "nickname": "America West",
    "destination": "OAK",
    "flight_count": 162
  },
  {
    "nickname": "USAir",
    "destination": "SFO",
    "flight_count": 156
  },
  {
    "nickname": "American",
    "destination": "OAK",
    "flight_count": 141
  },
  {
    "nickname": "Delta",
    "destination": "SFO",
    "flight_count": 132
  },
  {
    "nickname": "Southwest",
    "destination": "SFO",
    "flight_count": 86
  },
  {
    "nickname": "ATA",
    "destination": "SFO",
    "flight_count": 37
  },
  {
    "nickname": "Continental",
    "destination": "SJC",
    "flight_count": 35
  },
  {
    "nickname": "Jetblue",
    "destination": "SJC",
    "flight_count": 21
  },
  {
    "nickname": "Continental",
    "destination": "SFO",
    "flight_count": 21
  },
  {
    "nickname": "Delta",
    "destination": "SJC",
    "flight_count": 19
  },
  {
    "nickname": "Delta",
    "destination": "OAK",
    "flight_count": 10
  },
  {
    "nickname": "ATA",
    "destination": "SJC",
    "flight_count": 9
  },
  {
    "nickname": "Continental",
    "destination": "OAK",
    "flight_count": 8
  }
]
SELECT 
   carriers_0."nickname" as "nickname",
   base."destination" as "destination",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
WHERE base."destination" IN ('SFO','OAK','SJC')
GROUP BY 1,2
ORDER BY 3 desc NULLS LAST

Controlling Behavior with Tag Parameters

You can explicitly set any of the parameters in the bar_chart tag. The following parameters are supported:

  • x

  • y

  • series

document
# bar_chart { x=nickname series=destination y=flight_count }
run: flights -> {
  where: destination ? 'SFO' | 'OAK' | 'SJC'
  group_by: destination, carriers.nickname
  aggregate: flight_count
}
QUERY RESULTS
[
  {
    "destination": "OAK",
    "nickname": "Southwest",
    "flight_count": 3967
  },
  {
    "destination": "SFO",
    "nickname": "United",
    "flight_count": 2432
  },
  {
    "destination": "SJC",
    "nickname": "Southwest",
    "flight_count": 2143
  },
  {
    "destination": "SFO",
    "nickname": "American",
    "flight_count": 686
  },
  {
    "destination": "SJC",
    "nickname": "American",
    "flight_count": 558
  },
  {
    "destination": "SFO",
    "nickname": "Northwest",
    "flight_count": 520
  },
  {
    "destination": "SJC",
    "nickname": "Alaska",
    "flight_count": 367
  },
  {
    "destination": "SJC",
    "nickname": "United",
    "flight_count": 318
  },
  {
    "destination": "OAK",
    "nickname": "Jetblue",
    "flight_count": 297
  },
  {
    "destination": "OAK",
    "nickname": "United",
    "flight_count": 249
  },
  {
    "destination": "OAK",
    "nickname": "Alaska",
    "flight_count": 248
  },
  {
    "destination": "SFO",
    "nickname": "Alaska",
    "flight_count": 223
  },
  {
    "destination": "SFO",
    "nickname": "America West",
    "flight_count": 209
  },
  {
    "destination": "SJC",
    "nickname": "America West",
    "flight_count": 180
  },
  {
    "destination": "SJC",
    "nickname": "Northwest",
    "flight_count": 168
  },
  {
    "destination": "OAK",
    "nickname": "America West",
    "flight_count": 162
  },
  {
    "destination": "SFO",
    "nickname": "USAir",
    "flight_count": 156
  },
  {
    "destination": "OAK",
    "nickname": "American",
    "flight_count": 141
  },
  {
    "destination": "SFO",
    "nickname": "Delta",
    "flight_count": 132
  },
  {
    "destination": "SFO",
    "nickname": "Southwest",
    "flight_count": 86
  },
  {
    "destination": "SFO",
    "nickname": "ATA",
    "flight_count": 37
  },
  {
    "destination": "SJC",
    "nickname": "Continental",
    "flight_count": 35
  },
  {
    "destination": "SJC",
    "nickname": "Jetblue",
    "flight_count": 21
  },
  {
    "destination": "SFO",
    "nickname": "Continental",
    "flight_count": 21
  },
  {
    "destination": "SJC",
    "nickname": "Delta",
    "flight_count": 19
  },
  {
    "destination": "OAK",
    "nickname": "Delta",
    "flight_count": 10
  },
  {
    "destination": "SJC",
    "nickname": "ATA",
    "flight_count": 9
  },
  {
    "destination": "OAK",
    "nickname": "Continental",
    "flight_count": 8
  }
]
SELECT 
   base."destination" as "destination",
   carriers_0."nickname" as "nickname",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
WHERE base."destination" IN ('SFO','OAK','SJC')
GROUP BY 1,2
ORDER BY 3 desc NULLS LAST

Breakdown Chart with Multiple Measures

To create a breakdown chart with multiple measures as the series, provide y as an array of field names.

document
# bar_chart { x=nickname y=['to_sfo_flight_count', 'to_oak_flight_count'] }
run: flights -> {
  group_by: carriers.nickname
  aggregate: 
    to_sfo_flight_count
    to_oak_flight_count
}
QUERY RESULTS
[
  {
    "nickname": "United",
    "to_sfo_flight_count": 2432,
    "to_oak_flight_count": 249
  },
  {
    "nickname": "American",
    "to_sfo_flight_count": 686,
    "to_oak_flight_count": 141
  },
  {
    "nickname": "Northwest",
    "to_sfo_flight_count": 520,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Alaska",
    "to_sfo_flight_count": 223,
    "to_oak_flight_count": 248
  },
  {
    "nickname": "America West",
    "to_sfo_flight_count": 209,
    "to_oak_flight_count": 162
  },
  {
    "nickname": "USAir",
    "to_sfo_flight_count": 156,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Delta",
    "to_sfo_flight_count": 132,
    "to_oak_flight_count": 10
  },
  {
    "nickname": "Southwest",
    "to_sfo_flight_count": 86,
    "to_oak_flight_count": 3967
  },
  {
    "nickname": "ATA",
    "to_sfo_flight_count": 37,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Continental",
    "to_sfo_flight_count": 21,
    "to_oak_flight_count": 8
  },
  {
    "nickname": "Comair",
    "to_sfo_flight_count": 0,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Atlantic Southeast",
    "to_sfo_flight_count": 0,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Continental Express",
    "to_sfo_flight_count": 0,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "American Eagle",
    "to_sfo_flight_count": 0,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Jetblue",
    "to_sfo_flight_count": 0,
    "to_oak_flight_count": 297
  }
]
SELECT 
   carriers_0."nickname" as "nickname",
   (COUNT(CASE WHEN base."destination"='SFO' THEN 1 END)) as "to_sfo_flight_count",
   (COUNT(CASE WHEN base."destination"='OAK' THEN 1 END)) as "to_oak_flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Embedded Parameters

Parameters can also be embedded directly in a query by tagging fields:

document
# bar_chart
run: flights -> {
  where: destination ? 'SFO' | 'OAK' | 'SJC'
  group_by: 
    # series
    destination
    # x
    carriers.nickname
  aggregate:
    # y
    flight_count
    aircraft_count
}
QUERY RESULTS
[
  {
    "destination": "OAK",
    "nickname": "Southwest",
    "flight_count": 3967,
    "aircraft_count": 8
  },
  {
    "destination": "SFO",
    "nickname": "United",
    "flight_count": 2432,
    "aircraft_count": 6
  },
  {
    "destination": "SJC",
    "nickname": "Southwest",
    "flight_count": 2143,
    "aircraft_count": 8
  },
  {
    "destination": "SFO",
    "nickname": "American",
    "flight_count": 686,
    "aircraft_count": 6
  },
  {
    "destination": "SJC",
    "nickname": "American",
    "flight_count": 558,
    "aircraft_count": 5
  },
  {
    "destination": "SFO",
    "nickname": "Northwest",
    "flight_count": 520,
    "aircraft_count": 5
  },
  {
    "destination": "SJC",
    "nickname": "Alaska",
    "flight_count": 367,
    "aircraft_count": 1
  },
  {
    "destination": "SJC",
    "nickname": "United",
    "flight_count": 318,
    "aircraft_count": 5
  },
  {
    "destination": "OAK",
    "nickname": "Jetblue",
    "flight_count": 297,
    "aircraft_count": 2
  },
  {
    "destination": "OAK",
    "nickname": "United",
    "flight_count": 249,
    "aircraft_count": 5
  },
  {
    "destination": "OAK",
    "nickname": "Alaska",
    "flight_count": 248,
    "aircraft_count": 1
  },
  {
    "destination": "SFO",
    "nickname": "Alaska",
    "flight_count": 223,
    "aircraft_count": 1
  },
  {
    "destination": "SFO",
    "nickname": "America West",
    "flight_count": 209,
    "aircraft_count": 1
  },
  {
    "destination": "SJC",
    "nickname": "America West",
    "flight_count": 180,
    "aircraft_count": 1
  },
  {
    "destination": "SJC",
    "nickname": "Northwest",
    "flight_count": 168,
    "aircraft_count": 3
  },
  {
    "destination": "OAK",
    "nickname": "America West",
    "flight_count": 162,
    "aircraft_count": 1
  },
  {
    "destination": "SFO",
    "nickname": "USAir",
    "flight_count": 156,
    "aircraft_count": 2
  },
  {
    "destination": "OAK",
    "nickname": "American",
    "flight_count": 141,
    "aircraft_count": 4
  },
  {
    "destination": "SFO",
    "nickname": "Delta",
    "flight_count": 132,
    "aircraft_count": 3
  },
  {
    "destination": "SFO",
    "nickname": "Southwest",
    "flight_count": 86,
    "aircraft_count": 6
  },
  {
    "destination": "SFO",
    "nickname": "ATA",
    "flight_count": 37,
    "aircraft_count": 1
  },
  {
    "destination": "SJC",
    "nickname": "Continental",
    "flight_count": 35,
    "aircraft_count": 1
  },
  {
    "destination": "SJC",
    "nickname": "Jetblue",
    "flight_count": 21,
    "aircraft_count": 2
  },
  {
    "destination": "SFO",
    "nickname": "Continental",
    "flight_count": 21,
    "aircraft_count": 1
  },
  {
    "destination": "SJC",
    "nickname": "Delta",
    "flight_count": 19,
    "aircraft_count": 1
  },
  {
    "destination": "OAK",
    "nickname": "Delta",
    "flight_count": 10,
    "aircraft_count": 1
  },
  {
    "destination": "SJC",
    "nickname": "ATA",
    "flight_count": 9,
    "aircraft_count": 1
  },
  {
    "destination": "OAK",
    "nickname": "Continental",
    "flight_count": 8,
    "aircraft_count": 1
  }
]
SELECT 
   base."destination" as "destination",
   carriers_0."nickname" as "nickname",
   COUNT(1) as "flight_count",
   count(distinct base."tail_num") as "aircraft_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
WHERE base."destination" IN ('SFO','OAK','SJC')
GROUP BY 1,2
ORDER BY 3 desc NULLS LAST

For measure series, tag multiple aggregates as y by placing the tag above the aggregate: keyword:

document
# bar_chart
run: flights -> {
  group_by: carriers.nickname
  # y
  aggregate: 
    to_sfo_flight_count
    to_oak_flight_count
}
QUERY RESULTS
[
  {
    "nickname": "United",
    "to_sfo_flight_count": 2432,
    "to_oak_flight_count": 249
  },
  {
    "nickname": "American",
    "to_sfo_flight_count": 686,
    "to_oak_flight_count": 141
  },
  {
    "nickname": "Northwest",
    "to_sfo_flight_count": 520,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Alaska",
    "to_sfo_flight_count": 223,
    "to_oak_flight_count": 248
  },
  {
    "nickname": "America West",
    "to_sfo_flight_count": 209,
    "to_oak_flight_count": 162
  },
  {
    "nickname": "USAir",
    "to_sfo_flight_count": 156,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Delta",
    "to_sfo_flight_count": 132,
    "to_oak_flight_count": 10
  },
  {
    "nickname": "Southwest",
    "to_sfo_flight_count": 86,
    "to_oak_flight_count": 3967
  },
  {
    "nickname": "ATA",
    "to_sfo_flight_count": 37,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Continental",
    "to_sfo_flight_count": 21,
    "to_oak_flight_count": 8
  },
  {
    "nickname": "Continental Express",
    "to_sfo_flight_count": 0,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "American Eagle",
    "to_sfo_flight_count": 0,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Comair",
    "to_sfo_flight_count": 0,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Atlantic Southeast",
    "to_sfo_flight_count": 0,
    "to_oak_flight_count": 0
  },
  {
    "nickname": "Jetblue",
    "to_sfo_flight_count": 0,
    "to_oak_flight_count": 297
  }
]
SELECT 
   carriers_0."nickname" as "nickname",
   (COUNT(CASE WHEN base."destination"='SFO' THEN 1 END)) as "to_sfo_flight_count",
   (COUNT(CASE WHEN base."destination"='OAK' THEN 1 END)) as "to_oak_flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Nested Bar Charts

Bar charts can be used inside of nested queries:

document
run: flights -> {
  group_by: carriers.nickname
  aggregate: flight_count 
  # bar_chart.stack
  nest: top_destinations is  {
    group_by: destination
    # y
    aggregate:
      from_sfo_flight_count
      from_oak_flight_count
    limit: 10
  }
  # bar_chart
  nest: by_hour_of_day is  {
    group_by: hour_of_day is hour(dep_time)
    aggregate:
      flight_count
  }
}
QUERY RESULTS
[
  {
    "nickname": "Southwest",
    "flight_count": 88751,
    "top_destinations": [
      {
        "destination": "SAN",
        "from_sfo_flight_count": 45,
        "from_oak_flight_count": 479
      },
      {
        "destination": "PHX",
        "from_sfo_flight_count": 41,
        "from_oak_flight_count": 167
      },
      {
        "destination": "BHM",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BUF",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "GEG",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 32
      },
      {
        "destination": "LAX",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 745
      },
      {
        "destination": "MAF",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "OAK",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ORF",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "SAT",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 7,
        "flight_count": 6874
      },
      {
        "hour_of_day": 8,
        "flight_count": 6229
      },
      {
        "hour_of_day": 17,
        "flight_count": 6058
      },
      {
        "hour_of_day": 18,
        "flight_count": 5688
      },
      {
        "hour_of_day": 9,
        "flight_count": 5623
      },
      {
        "hour_of_day": 16,
        "flight_count": 5598
      },
      {
        "hour_of_day": 12,
        "flight_count": 5552
      },
      {
        "hour_of_day": 11,
        "flight_count": 5538
      },
      {
        "hour_of_day": 19,
        "flight_count": 5433
      },
      {
        "hour_of_day": 13,
        "flight_count": 5420
      },
      {
        "hour_of_day": 14,
        "flight_count": 5319
      },
      {
        "hour_of_day": 10,
        "flight_count": 5291
      },
      {
        "hour_of_day": 15,
        "flight_count": 5266
      },
      {
        "hour_of_day": 20,
        "flight_count": 4907
      },
      {
        "hour_of_day": 6,
        "flight_count": 4421
      },
      {
        "hour_of_day": 21,
        "flight_count": 3741
      },
      {
        "hour_of_day": 22,
        "flight_count": 1335
      },
      {
        "hour_of_day": 23,
        "flight_count": 229
      },
      {
        "hour_of_day": 5,
        "flight_count": 175
      },
      {
        "hour_of_day": 0,
        "flight_count": 36
      },
      {
        "hour_of_day": 1,
        "flight_count": 14
      },
      {
        "hour_of_day": 2,
        "flight_count": 3
      },
      {
        "hour_of_day": 3,
        "flight_count": 1
      }
    ]
  },
  {
    "nickname": "USAir",
    "flight_count": 37683,
    "top_destinations": [
      {
        "destination": "CLT",
        "from_sfo_flight_count": 49,
        "from_oak_flight_count": 0
      },
      {
        "destination": "PHL",
        "from_sfo_flight_count": 44,
        "from_oak_flight_count": 0
      },
      {
        "destination": "PIT",
        "from_sfo_flight_count": 40,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BWI",
        "from_sfo_flight_count": 24,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ABE",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ATL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "AVL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "AVP",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BOS",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BTV",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 15,
        "flight_count": 2718
      },
      {
        "hour_of_day": 7,
        "flight_count": 2667
      },
      {
        "hour_of_day": 17,
        "flight_count": 2608
      },
      {
        "hour_of_day": 11,
        "flight_count": 2477
      },
      {
        "hour_of_day": 8,
        "flight_count": 2455
      },
      {
        "hour_of_day": 13,
        "flight_count": 2362
      },
      {
        "hour_of_day": 18,
        "flight_count": 2310
      },
      {
        "hour_of_day": 9,
        "flight_count": 2307
      },
      {
        "hour_of_day": 6,
        "flight_count": 2262
      },
      {
        "hour_of_day": 16,
        "flight_count": 2147
      },
      {
        "hour_of_day": 10,
        "flight_count": 2142
      },
      {
        "hour_of_day": 12,
        "flight_count": 2140
      },
      {
        "hour_of_day": 14,
        "flight_count": 1980
      },
      {
        "hour_of_day": 19,
        "flight_count": 1972
      },
      {
        "hour_of_day": 20,
        "flight_count": 1800
      },
      {
        "hour_of_day": 21,
        "flight_count": 1219
      },
      {
        "hour_of_day": 22,
        "flight_count": 1108
      },
      {
        "hour_of_day": 5,
        "flight_count": 621
      },
      {
        "hour_of_day": 23,
        "flight_count": 276
      },
      {
        "hour_of_day": 0,
        "flight_count": 61
      },
      {
        "hour_of_day": 1,
        "flight_count": 32
      },
      {
        "hour_of_day": 2,
        "flight_count": 9
      },
      {
        "hour_of_day": 4,
        "flight_count": 8
      },
      {
        "hour_of_day": 3,
        "flight_count": 2
      }
    ]
  },
  {
    "nickname": "American",
    "flight_count": 34577,
    "top_destinations": [
      {
        "destination": "DFW",
        "from_sfo_flight_count": 252,
        "from_oak_flight_count": 128
      },
      {
        "destination": "JFK",
        "from_sfo_flight_count": 148,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LAX",
        "from_sfo_flight_count": 93,
        "from_oak_flight_count": 11
      },
      {
        "destination": "ORD",
        "from_sfo_flight_count": 80,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BOS",
        "from_sfo_flight_count": 49,
        "from_oak_flight_count": 0
      },
      {
        "destination": "MIA",
        "from_sfo_flight_count": 32,
        "from_oak_flight_count": 0
      },
      {
        "destination": "STL",
        "from_sfo_flight_count": 17,
        "from_oak_flight_count": 0
      },
      {
        "destination": "HNL",
        "from_sfo_flight_count": 11,
        "from_oak_flight_count": 0
      },
      {
        "destination": "SNA",
        "from_sfo_flight_count": 6,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ATL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 8,
        "flight_count": 2727
      },
      {
        "hour_of_day": 9,
        "flight_count": 2332
      },
      {
        "hour_of_day": 6,
        "flight_count": 2319
      },
      {
        "hour_of_day": 17,
        "flight_count": 2286
      },
      {
        "hour_of_day": 7,
        "flight_count": 2271
      },
      {
        "hour_of_day": 12,
        "flight_count": 2232
      },
      {
        "hour_of_day": 14,
        "flight_count": 2173
      },
      {
        "hour_of_day": 15,
        "flight_count": 2167
      },
      {
        "hour_of_day": 10,
        "flight_count": 2165
      },
      {
        "hour_of_day": 11,
        "flight_count": 2149
      },
      {
        "hour_of_day": 13,
        "flight_count": 2136
      },
      {
        "hour_of_day": 18,
        "flight_count": 2019
      },
      {
        "hour_of_day": 16,
        "flight_count": 1992
      },
      {
        "hour_of_day": 19,
        "flight_count": 1906
      },
      {
        "hour_of_day": 20,
        "flight_count": 1456
      },
      {
        "hour_of_day": 22,
        "flight_count": 682
      },
      {
        "hour_of_day": 21,
        "flight_count": 658
      },
      {
        "hour_of_day": 5,
        "flight_count": 432
      },
      {
        "hour_of_day": 23,
        "flight_count": 277
      },
      {
        "hour_of_day": 0,
        "flight_count": 158
      },
      {
        "hour_of_day": 1,
        "flight_count": 22
      },
      {
        "hour_of_day": 2,
        "flight_count": 13
      },
      {
        "hour_of_day": 3,
        "flight_count": 5
      }
    ]
  },
  {
    "nickname": "Northwest",
    "flight_count": 33580,
    "top_destinations": [
      {
        "destination": "MSP",
        "from_sfo_flight_count": 281,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DTW",
        "from_sfo_flight_count": 134,
        "from_oak_flight_count": 0
      },
      {
        "destination": "MEM",
        "from_sfo_flight_count": 81,
        "from_oak_flight_count": 0
      },
      {
        "destination": "HNL",
        "from_sfo_flight_count": 26,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ABQ",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ALB",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ANC",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ATL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BIL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BWI",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 9,
        "flight_count": 3048
      },
      {
        "hour_of_day": 13,
        "flight_count": 2553
      },
      {
        "hour_of_day": 12,
        "flight_count": 2470
      },
      {
        "hour_of_day": 17,
        "flight_count": 2424
      },
      {
        "hour_of_day": 6,
        "flight_count": 2306
      },
      {
        "hour_of_day": 8,
        "flight_count": 2267
      },
      {
        "hour_of_day": 7,
        "flight_count": 2046
      },
      {
        "hour_of_day": 15,
        "flight_count": 1873
      },
      {
        "hour_of_day": 19,
        "flight_count": 1846
      },
      {
        "hour_of_day": 18,
        "flight_count": 1771
      },
      {
        "hour_of_day": 14,
        "flight_count": 1713
      },
      {
        "hour_of_day": 11,
        "flight_count": 1708
      },
      {
        "hour_of_day": 21,
        "flight_count": 1589
      },
      {
        "hour_of_day": 16,
        "flight_count": 1501
      },
      {
        "hour_of_day": 10,
        "flight_count": 1358
      },
      {
        "hour_of_day": 20,
        "flight_count": 872
      },
      {
        "hour_of_day": 22,
        "flight_count": 816
      },
      {
        "hour_of_day": 0,
        "flight_count": 785
      },
      {
        "hour_of_day": 5,
        "flight_count": 318
      },
      {
        "hour_of_day": 23,
        "flight_count": 159
      },
      {
        "hour_of_day": 1,
        "flight_count": 136
      },
      {
        "hour_of_day": 2,
        "flight_count": 18
      },
      {
        "hour_of_day": 4,
        "flight_count": 2
      },
      {
        "hour_of_day": 3,
        "flight_count": 1
      }
    ]
  },
  {
    "nickname": "United",
    "flight_count": 32757,
    "top_destinations": [
      {
        "destination": "LAS",
        "from_sfo_flight_count": 299,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ORD",
        "from_sfo_flight_count": 282,
        "from_oak_flight_count": 104
      },
      {
        "destination": "IAD",
        "from_sfo_flight_count": 235,
        "from_oak_flight_count": 54
      },
      {
        "destination": "DEN",
        "from_sfo_flight_count": 194,
        "from_oak_flight_count": 55
      },
      {
        "destination": "LAX",
        "from_sfo_flight_count": 182,
        "from_oak_flight_count": 36
      },
      {
        "destination": "SNA",
        "from_sfo_flight_count": 150,
        "from_oak_flight_count": 0
      },
      {
        "destination": "PHX",
        "from_sfo_flight_count": 140,
        "from_oak_flight_count": 0
      },
      {
        "destination": "PHL",
        "from_sfo_flight_count": 123,
        "from_oak_flight_count": 0
      },
      {
        "destination": "SAN",
        "from_sfo_flight_count": 118,
        "from_oak_flight_count": 0
      },
      {
        "destination": "EWR",
        "from_sfo_flight_count": 105,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 8,
        "flight_count": 3001
      },
      {
        "hour_of_day": 17,
        "flight_count": 2552
      },
      {
        "hour_of_day": 6,
        "flight_count": 2358
      },
      {
        "hour_of_day": 11,
        "flight_count": 2186
      },
      {
        "hour_of_day": 18,
        "flight_count": 2172
      },
      {
        "hour_of_day": 10,
        "flight_count": 2076
      },
      {
        "hour_of_day": 7,
        "flight_count": 1948
      },
      {
        "hour_of_day": 12,
        "flight_count": 1897
      },
      {
        "hour_of_day": 9,
        "flight_count": 1882
      },
      {
        "hour_of_day": 14,
        "flight_count": 1855
      },
      {
        "hour_of_day": 13,
        "flight_count": 1833
      },
      {
        "hour_of_day": 16,
        "flight_count": 1730
      },
      {
        "hour_of_day": 15,
        "flight_count": 1656
      },
      {
        "hour_of_day": 19,
        "flight_count": 1508
      },
      {
        "hour_of_day": 20,
        "flight_count": 1227
      },
      {
        "hour_of_day": 21,
        "flight_count": 1010
      },
      {
        "hour_of_day": 22,
        "flight_count": 752
      },
      {
        "hour_of_day": 5,
        "flight_count": 560
      },
      {
        "hour_of_day": 23,
        "flight_count": 434
      },
      {
        "hour_of_day": 0,
        "flight_count": 110
      },
      {
        "hour_of_day": 1,
        "flight_count": 10
      }
    ]
  },
  {
    "nickname": "Delta",
    "flight_count": 32130,
    "top_destinations": [
      {
        "destination": "ATL",
        "from_sfo_flight_count": 97,
        "from_oak_flight_count": 10
      },
      {
        "destination": "CVG",
        "from_sfo_flight_count": 17,
        "from_oak_flight_count": 0
      },
      {
        "destination": "JFK",
        "from_sfo_flight_count": 7,
        "from_oak_flight_count": 0
      },
      {
        "destination": "HNL",
        "from_sfo_flight_count": 5,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LAX",
        "from_sfo_flight_count": 3,
        "from_oak_flight_count": 0
      },
      {
        "destination": "SLC",
        "from_sfo_flight_count": 3,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DFW",
        "from_sfo_flight_count": 2,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ABE",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ANC",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BNA",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 8,
        "flight_count": 2554
      },
      {
        "hour_of_day": 10,
        "flight_count": 2293
      },
      {
        "hour_of_day": 12,
        "flight_count": 2184
      },
      {
        "hour_of_day": 16,
        "flight_count": 2163
      },
      {
        "hour_of_day": 17,
        "flight_count": 2060
      },
      {
        "hour_of_day": 14,
        "flight_count": 2022
      },
      {
        "hour_of_day": 19,
        "flight_count": 1926
      },
      {
        "hour_of_day": 18,
        "flight_count": 1918
      },
      {
        "hour_of_day": 13,
        "flight_count": 1870
      },
      {
        "hour_of_day": 11,
        "flight_count": 1839
      },
      {
        "hour_of_day": 6,
        "flight_count": 1660
      },
      {
        "hour_of_day": 20,
        "flight_count": 1655
      },
      {
        "hour_of_day": 7,
        "flight_count": 1586
      },
      {
        "hour_of_day": 15,
        "flight_count": 1570
      },
      {
        "hour_of_day": 9,
        "flight_count": 1376
      },
      {
        "hour_of_day": 0,
        "flight_count": 1144
      },
      {
        "hour_of_day": 21,
        "flight_count": 897
      },
      {
        "hour_of_day": 22,
        "flight_count": 633
      },
      {
        "hour_of_day": 5,
        "flight_count": 409
      },
      {
        "hour_of_day": 23,
        "flight_count": 311
      },
      {
        "hour_of_day": 1,
        "flight_count": 43
      },
      {
        "hour_of_day": 2,
        "flight_count": 7
      },
      {
        "hour_of_day": 4,
        "flight_count": 6
      },
      {
        "hour_of_day": 3,
        "flight_count": 4
      }
    ]
  },
  {
    "nickname": "Continental Express",
    "flight_count": 16074,
    "top_destinations": [
      {
        "destination": "ABE",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ABI",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ALB",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BGR",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BPT",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BRO",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "CRW",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DCA",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DFW",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DRO",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 15,
        "flight_count": 1418
      },
      {
        "hour_of_day": 13,
        "flight_count": 1348
      },
      {
        "hour_of_day": 9,
        "flight_count": 1330
      },
      {
        "hour_of_day": 6,
        "flight_count": 1171
      },
      {
        "hour_of_day": 11,
        "flight_count": 1171
      },
      {
        "hour_of_day": 17,
        "flight_count": 1171
      },
      {
        "hour_of_day": 18,
        "flight_count": 1016
      },
      {
        "hour_of_day": 12,
        "flight_count": 1014
      },
      {
        "hour_of_day": 14,
        "flight_count": 982
      },
      {
        "hour_of_day": 16,
        "flight_count": 938
      },
      {
        "hour_of_day": 7,
        "flight_count": 933
      },
      {
        "hour_of_day": 10,
        "flight_count": 851
      },
      {
        "hour_of_day": 20,
        "flight_count": 772
      },
      {
        "hour_of_day": 19,
        "flight_count": 744
      },
      {
        "hour_of_day": 8,
        "flight_count": 675
      },
      {
        "hour_of_day": 21,
        "flight_count": 266
      },
      {
        "hour_of_day": 5,
        "flight_count": 189
      },
      {
        "hour_of_day": 22,
        "flight_count": 63
      },
      {
        "hour_of_day": 23,
        "flight_count": 13
      },
      {
        "hour_of_day": 0,
        "flight_count": 5
      },
      {
        "hour_of_day": 1,
        "flight_count": 4
      }
    ]
  },
  {
    "nickname": "American Eagle",
    "flight_count": 15869,
    "top_destinations": [
      {
        "destination": "BGR",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BNA",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DSM",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "GGG",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "HPN",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LAW",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LBB",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LIT",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LRD",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "MEM",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 11,
        "flight_count": 1242
      },
      {
        "hour_of_day": 14,
        "flight_count": 1209
      },
      {
        "hour_of_day": 12,
        "flight_count": 1149
      },
      {
        "hour_of_day": 16,
        "flight_count": 1137
      },
      {
        "hour_of_day": 9,
        "flight_count": 1137
      },
      {
        "hour_of_day": 13,
        "flight_count": 1107
      },
      {
        "hour_of_day": 15,
        "flight_count": 1106
      },
      {
        "hour_of_day": 17,
        "flight_count": 1071
      },
      {
        "hour_of_day": 10,
        "flight_count": 1056
      },
      {
        "hour_of_day": 8,
        "flight_count": 1029
      },
      {
        "hour_of_day": 18,
        "flight_count": 882
      },
      {
        "hour_of_day": 6,
        "flight_count": 851
      },
      {
        "hour_of_day": 19,
        "flight_count": 775
      },
      {
        "hour_of_day": 7,
        "flight_count": 740
      },
      {
        "hour_of_day": 20,
        "flight_count": 601
      },
      {
        "hour_of_day": 21,
        "flight_count": 338
      },
      {
        "hour_of_day": 5,
        "flight_count": 279
      },
      {
        "hour_of_day": 22,
        "flight_count": 115
      },
      {
        "hour_of_day": 23,
        "flight_count": 42
      },
      {
        "hour_of_day": 1,
        "flight_count": 2
      },
      {
        "hour_of_day": 0,
        "flight_count": 1
      }
    ]
  },
  {
    "nickname": "Atlantic Southeast",
    "flight_count": 15769,
    "top_destinations": [
      {
        "destination": "ABY",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "AGS",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ATL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "AVL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "CAE",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "CHA",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DFW",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LIT",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "MCN",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "MGM",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 10,
        "flight_count": 1083
      },
      {
        "hour_of_day": 13,
        "flight_count": 1035
      },
      {
        "hour_of_day": 18,
        "flight_count": 1027
      },
      {
        "hour_of_day": 16,
        "flight_count": 1023
      },
      {
        "hour_of_day": 12,
        "flight_count": 949
      },
      {
        "hour_of_day": 11,
        "flight_count": 900
      },
      {
        "hour_of_day": 19,
        "flight_count": 883
      },
      {
        "hour_of_day": 14,
        "flight_count": 868
      },
      {
        "hour_of_day": 9,
        "flight_count": 856
      },
      {
        "hour_of_day": 8,
        "flight_count": 844
      },
      {
        "hour_of_day": 15,
        "flight_count": 832
      },
      {
        "hour_of_day": 17,
        "flight_count": 818
      },
      {
        "hour_of_day": 7,
        "flight_count": 814
      },
      {
        "hour_of_day": 20,
        "flight_count": 704
      },
      {
        "hour_of_day": 22,
        "flight_count": 703
      },
      {
        "hour_of_day": 6,
        "flight_count": 665
      },
      {
        "hour_of_day": 0,
        "flight_count": 600
      },
      {
        "hour_of_day": 21,
        "flight_count": 515
      },
      {
        "hour_of_day": 5,
        "flight_count": 389
      },
      {
        "hour_of_day": 23,
        "flight_count": 230
      },
      {
        "hour_of_day": 4,
        "flight_count": 29
      },
      {
        "hour_of_day": 1,
        "flight_count": 2
      }
    ]
  },
  {
    "nickname": "America West",
    "flight_count": 9750,
    "top_destinations": [
      {
        "destination": "LAS",
        "from_sfo_flight_count": 132,
        "from_oak_flight_count": 40
      },
      {
        "destination": "PHX",
        "from_sfo_flight_count": 78,
        "from_oak_flight_count": 124
      },
      {
        "destination": "ATL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BOI",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DFW",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DTW",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ELP",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LAX",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "MKE",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "MSY",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 9,
        "flight_count": 699
      },
      {
        "hour_of_day": 8,
        "flight_count": 684
      },
      {
        "hour_of_day": 17,
        "flight_count": 684
      },
      {
        "hour_of_day": 14,
        "flight_count": 659
      },
      {
        "hour_of_day": 18,
        "flight_count": 636
      },
      {
        "hour_of_day": 11,
        "flight_count": 624
      },
      {
        "hour_of_day": 16,
        "flight_count": 567
      },
      {
        "hour_of_day": 7,
        "flight_count": 555
      },
      {
        "hour_of_day": 15,
        "flight_count": 508
      },
      {
        "hour_of_day": 19,
        "flight_count": 504
      },
      {
        "hour_of_day": 20,
        "flight_count": 485
      },
      {
        "hour_of_day": 12,
        "flight_count": 478
      },
      {
        "hour_of_day": 13,
        "flight_count": 463
      },
      {
        "hour_of_day": 10,
        "flight_count": 461
      },
      {
        "hour_of_day": 6,
        "flight_count": 439
      },
      {
        "hour_of_day": 21,
        "flight_count": 423
      },
      {
        "hour_of_day": 0,
        "flight_count": 328
      },
      {
        "hour_of_day": 23,
        "flight_count": 207
      },
      {
        "hour_of_day": 22,
        "flight_count": 176
      },
      {
        "hour_of_day": 5,
        "flight_count": 122
      },
      {
        "hour_of_day": 1,
        "flight_count": 40
      },
      {
        "hour_of_day": 2,
        "flight_count": 5
      },
      {
        "hour_of_day": 3,
        "flight_count": 3
      }
    ]
  },
  {
    "nickname": "Alaska",
    "flight_count": 8453,
    "top_destinations": [
      {
        "destination": "SEA",
        "from_sfo_flight_count": 95,
        "from_oak_flight_count": 201
      },
      {
        "destination": "PDX",
        "from_sfo_flight_count": 66,
        "from_oak_flight_count": 44
      },
      {
        "destination": "LAX",
        "from_sfo_flight_count": 40,
        "from_oak_flight_count": 0
      },
      {
        "destination": "PSP",
        "from_sfo_flight_count": 20,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BUR",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "FAI",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LAS",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "SFO",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "SJC",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "ANC",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 6,
        "flight_count": 836
      },
      {
        "hour_of_day": 7,
        "flight_count": 603
      },
      {
        "hour_of_day": 10,
        "flight_count": 591
      },
      {
        "hour_of_day": 20,
        "flight_count": 575
      },
      {
        "hour_of_day": 13,
        "flight_count": 574
      },
      {
        "hour_of_day": 19,
        "flight_count": 565
      },
      {
        "hour_of_day": 17,
        "flight_count": 555
      },
      {
        "hour_of_day": 9,
        "flight_count": 507
      },
      {
        "hour_of_day": 15,
        "flight_count": 490
      },
      {
        "hour_of_day": 18,
        "flight_count": 465
      },
      {
        "hour_of_day": 16,
        "flight_count": 450
      },
      {
        "hour_of_day": 12,
        "flight_count": 393
      },
      {
        "hour_of_day": 21,
        "flight_count": 380
      },
      {
        "hour_of_day": 11,
        "flight_count": 344
      },
      {
        "hour_of_day": 14,
        "flight_count": 343
      },
      {
        "hour_of_day": 8,
        "flight_count": 333
      },
      {
        "hour_of_day": 22,
        "flight_count": 160
      },
      {
        "hour_of_day": 23,
        "flight_count": 132
      },
      {
        "hour_of_day": 1,
        "flight_count": 56
      },
      {
        "hour_of_day": 0,
        "flight_count": 48
      },
      {
        "hour_of_day": 5,
        "flight_count": 26
      },
      {
        "hour_of_day": 2,
        "flight_count": 23
      },
      {
        "hour_of_day": 3,
        "flight_count": 4
      }
    ]
  },
  {
    "nickname": "Continental",
    "flight_count": 7139,
    "top_destinations": [
      {
        "destination": "IAH",
        "from_sfo_flight_count": 19,
        "from_oak_flight_count": 8
      },
      {
        "destination": "CLE",
        "from_sfo_flight_count": 2,
        "from_oak_flight_count": 0
      },
      {
        "destination": "AUS",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BDL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BOS",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BRO",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BTR",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BWI",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DTW",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "GPT",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 9,
        "flight_count": 619
      },
      {
        "hour_of_day": 15,
        "flight_count": 608
      },
      {
        "hour_of_day": 7,
        "flight_count": 587
      },
      {
        "hour_of_day": 17,
        "flight_count": 552
      },
      {
        "hour_of_day": 13,
        "flight_count": 536
      },
      {
        "hour_of_day": 11,
        "flight_count": 527
      },
      {
        "hour_of_day": 6,
        "flight_count": 462
      },
      {
        "hour_of_day": 14,
        "flight_count": 430
      },
      {
        "hour_of_day": 12,
        "flight_count": 428
      },
      {
        "hour_of_day": 18,
        "flight_count": 414
      },
      {
        "hour_of_day": 10,
        "flight_count": 394
      },
      {
        "hour_of_day": 16,
        "flight_count": 387
      },
      {
        "hour_of_day": 20,
        "flight_count": 330
      },
      {
        "hour_of_day": 19,
        "flight_count": 329
      },
      {
        "hour_of_day": 8,
        "flight_count": 302
      },
      {
        "hour_of_day": 21,
        "flight_count": 134
      },
      {
        "hour_of_day": 5,
        "flight_count": 64
      },
      {
        "hour_of_day": 22,
        "flight_count": 26
      },
      {
        "hour_of_day": 23,
        "flight_count": 5
      },
      {
        "hour_of_day": 0,
        "flight_count": 3
      },
      {
        "hour_of_day": 3,
        "flight_count": 1
      },
      {
        "hour_of_day": 1,
        "flight_count": 1
      }
    ]
  },
  {
    "nickname": "Jetblue",
    "flight_count": 4842,
    "top_destinations": [
      {
        "destination": "DEN",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LGA",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "MCO",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "MSY",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "PHX",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "RSW",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "SAN",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "SLC",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "SMF",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "TPA",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 6,
        "flight_count": 396
      },
      {
        "hour_of_day": 11,
        "flight_count": 376
      },
      {
        "hour_of_day": 17,
        "flight_count": 315
      },
      {
        "hour_of_day": 7,
        "flight_count": 314
      },
      {
        "hour_of_day": 19,
        "flight_count": 314
      },
      {
        "hour_of_day": 10,
        "flight_count": 308
      },
      {
        "hour_of_day": 15,
        "flight_count": 275
      },
      {
        "hour_of_day": 8,
        "flight_count": 272
      },
      {
        "hour_of_day": 20,
        "flight_count": 266
      },
      {
        "hour_of_day": 13,
        "flight_count": 262
      },
      {
        "hour_of_day": 9,
        "flight_count": 261
      },
      {
        "hour_of_day": 16,
        "flight_count": 238
      },
      {
        "hour_of_day": 21,
        "flight_count": 231
      },
      {
        "hour_of_day": 14,
        "flight_count": 204
      },
      {
        "hour_of_day": 18,
        "flight_count": 194
      },
      {
        "hour_of_day": 12,
        "flight_count": 177
      },
      {
        "hour_of_day": 22,
        "flight_count": 175
      },
      {
        "hour_of_day": 23,
        "flight_count": 100
      },
      {
        "hour_of_day": 0,
        "flight_count": 82
      },
      {
        "hour_of_day": 5,
        "flight_count": 32
      },
      {
        "hour_of_day": 1,
        "flight_count": 28
      },
      {
        "hour_of_day": 2,
        "flight_count": 14
      },
      {
        "hour_of_day": 3,
        "flight_count": 6
      },
      {
        "hour_of_day": 4,
        "flight_count": 2
      }
    ]
  },
  {
    "nickname": "Comair",
    "flight_count": 4420,
    "top_destinations": [
      {
        "destination": "ATW",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "AVL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BHM",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "BTV",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "CVG",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "EWR",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "FNT",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "GRB",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "GSO",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "HTS",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 9,
        "flight_count": 357
      },
      {
        "hour_of_day": 15,
        "flight_count": 292
      },
      {
        "hour_of_day": 10,
        "flight_count": 276
      },
      {
        "hour_of_day": 12,
        "flight_count": 268
      },
      {
        "hour_of_day": 11,
        "flight_count": 258
      },
      {
        "hour_of_day": 19,
        "flight_count": 255
      },
      {
        "hour_of_day": 17,
        "flight_count": 249
      },
      {
        "hour_of_day": 16,
        "flight_count": 248
      },
      {
        "hour_of_day": 6,
        "flight_count": 248
      },
      {
        "hour_of_day": 13,
        "flight_count": 242
      },
      {
        "hour_of_day": 20,
        "flight_count": 238
      },
      {
        "hour_of_day": 18,
        "flight_count": 219
      },
      {
        "hour_of_day": 14,
        "flight_count": 215
      },
      {
        "hour_of_day": 8,
        "flight_count": 201
      },
      {
        "hour_of_day": 21,
        "flight_count": 191
      },
      {
        "hour_of_day": 7,
        "flight_count": 183
      },
      {
        "hour_of_day": 5,
        "flight_count": 161
      },
      {
        "hour_of_day": 23,
        "flight_count": 121
      },
      {
        "hour_of_day": 0,
        "flight_count": 113
      },
      {
        "hour_of_day": 22,
        "flight_count": 79
      },
      {
        "hour_of_day": 1,
        "flight_count": 5
      },
      {
        "hour_of_day": 4,
        "flight_count": 1
      }
    ]
  },
  {
    "nickname": "ATA",
    "flight_count": 3033,
    "top_destinations": [
      {
        "destination": "MDW",
        "from_sfo_flight_count": 30,
        "from_oak_flight_count": 0
      },
      {
        "destination": "IND",
        "from_sfo_flight_count": 4,
        "from_oak_flight_count": 0
      },
      {
        "destination": "EWR",
        "from_sfo_flight_count": 3,
        "from_oak_flight_count": 0
      },
      {
        "destination": "DFW",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LGA",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "MIA",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "SFO",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "CLT",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "FLL",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      },
      {
        "destination": "LAS",
        "from_sfo_flight_count": 0,
        "from_oak_flight_count": 0
      }
    ],
    "by_hour_of_day": [
      {
        "hour_of_day": 6,
        "flight_count": 301
      },
      {
        "hour_of_day": 18,
        "flight_count": 214
      },
      {
        "hour_of_day": 12,
        "flight_count": 212
      },
      {
        "hour_of_day": 10,
        "flight_count": 212
      },
      {
        "hour_of_day": 19,
        "flight_count": 201
      },
      {
        "hour_of_day": 14,
        "flight_count": 197
      },
      {
        "hour_of_day": 8,
        "flight_count": 183
      },
      {
        "hour_of_day": 13,
        "flight_count": 176
      },
      {
        "hour_of_day": 9,
        "flight_count": 164
      },
      {
        "hour_of_day": 15,
        "flight_count": 164
      },
      {
        "hour_of_day": 7,
        "flight_count": 159
      },
      {
        "hour_of_day": 17,
        "flight_count": 144
      },
      {
        "hour_of_day": 11,
        "flight_count": 124
      },
      {
        "hour_of_day": 16,
        "flight_count": 124
      },
      {
        "hour_of_day": 20,
        "flight_count": 114
      },
      {
        "hour_of_day": 21,
        "flight_count": 114
      },
      {
        "hour_of_day": 5,
        "flight_count": 79
      },
      {
        "hour_of_day": 22,
        "flight_count": 61
      },
      {
        "hour_of_day": 0,
        "flight_count": 45
      },
      {
        "hour_of_day": 23,
        "flight_count": 41
      },
      {
        "hour_of_day": 1,
        "flight_count": 3
      },
      {
        "hour_of_day": 3,
        "flight_count": 1
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    carriers_0."nickname" as "nickname__0",
    CASE WHEN group_set=0 THEN
      COUNT(1)
      END as "flight_count__0",
    CASE WHEN group_set=1 THEN
      base."destination"
      END as "destination__1",
    (CASE WHEN group_set=1 THEN
      COUNT(CASE WHEN base."origin"='SFO' THEN 1 END)
      END) as "from_sfo_flight_count__1",
    (CASE WHEN group_set=1 THEN
      COUNT(CASE WHEN base."origin"='OAK' THEN 1 END)
      END) as "from_oak_flight_count__1",
    CASE WHEN group_set=2 THEN
      EXTRACT(hour FROM base."dep_time")
      END as "hour_of_day__2",
    CASE WHEN group_set=2 THEN
      COUNT(1)
      END as "flight_count__2"
  FROM '../data/flights.parquet' as base
   LEFT JOIN '../data/carriers.parquet' AS carriers_0
    ON base."carrier"=carriers_0."code"
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set  ) as group_set
  GROUP BY 1,2,4,7
)
SELECT
  "nickname__0" as "nickname",
  MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count",
  COALESCE(LIST({
    "destination": "destination__1", 
    "from_sfo_flight_count": "from_sfo_flight_count__1", 
    "from_oak_flight_count": "from_oak_flight_count__1"}  ORDER BY  "from_sfo_flight_count__1" desc NULLS LAST) FILTER (WHERE group_set=1)[1:10],[]) as "top_destinations",
  COALESCE(LIST({
    "hour_of_day": "hour_of_day__2", 
    "flight_count": "flight_count__2"}  ORDER BY  "flight_count__2" desc NULLS LAST) FILTER (WHERE group_set=2),[]) as "by_hour_of_day"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST