What Is Malloy
Table Transpose
The # dashboard tag renders a query result as a dashboard layout. Dimensions appear at the top, while aggregates and nested views float within the dashboard.
Properties
| Property | Description | Example |
|---|---|---|
.table.max_height |
Max pixel height for tables in tiles | # dashboard { table.max_height=400 } |
# break |
Layout break (on a nested view) | Forces next item to a new row |
source: airports is duckdb.table('../data/airports.parquet') extend { measure: airport_count is count() view: by_state_and_county is { limit: 10 group_by: state aggregate: airport_count nest: by_fac_type is { group_by: fac_type aggregate: airport_count } } }
Queries in Malloy are often very complex and multifaceted, which makes them difficult to read in one nested table:
run: airports -> by_state_and_county[ { "state": "TX", "airport_count": 1845, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 1389 }, { "fac_type": "HELIPORT", "airport_count": 435 }, { "fac_type": "STOLPORT", "airport_count": 8 }, { "fac_type": "ULTRALIGHT", "airport_count": 8 }, { "fac_type": "GLIDERPORT", "airport_count": 5 } ] }, { "state": "CA", "airport_count": 984, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 569 }, { "fac_type": "HELIPORT", "airport_count": 396 }, { "fac_type": "SEAPLANE BASE", "airport_count": 12 }, { "fac_type": "GLIDERPORT", "airport_count": 3 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 }, { "fac_type": "STOLPORT", "airport_count": 2 } ] }, { "state": "IL", "airport_count": 890, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 625 }, { "fac_type": "HELIPORT", "airport_count": 245 }, { "fac_type": "SEAPLANE BASE", "airport_count": 8 }, { "fac_type": "ULTRALIGHT", "airport_count": 6 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "BALLOONPORT", "airport_count": 2 }, { "fac_type": "STOLPORT", "airport_count": 2 } ] }, { "state": "FL", "airport_count": 856, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 511 }, { "fac_type": "HELIPORT", "airport_count": 280 }, { "fac_type": "SEAPLANE BASE", "airport_count": 43 }, { "fac_type": "STOLPORT", "airport_count": 13 }, { "fac_type": "ULTRALIGHT", "airport_count": 5 }, { "fac_type": "GLIDERPORT", "airport_count": 4 } ] }, { "state": "PA", "airport_count": 804, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 468 }, { "fac_type": "HELIPORT", "airport_count": 307 }, { "fac_type": "ULTRALIGHT", "airport_count": 13 }, { "fac_type": "SEAPLANE BASE", "airport_count": 10 }, { "fac_type": "GLIDERPORT", "airport_count": 3 }, { "fac_type": "STOLPORT", "airport_count": 3 } ] }, { "state": "OH", "airport_count": 749, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 537 }, { "fac_type": "HELIPORT", "airport_count": 201 }, { "fac_type": "STOLPORT", "airport_count": 4 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 }, { "fac_type": "SEAPLANE BASE", "airport_count": 2 }, { "fac_type": "BALLOONPORT", "airport_count": 1 } ] }, { "state": "IN", "airport_count": 643, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 497 }, { "fac_type": "HELIPORT", "airport_count": 115 }, { "fac_type": "ULTRALIGHT", "airport_count": 17 }, { "fac_type": "SEAPLANE BASE", "airport_count": 12 }, { "fac_type": "STOLPORT", "airport_count": 2 } ] }, { "state": "AK", "airport_count": 608, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 474 }, { "fac_type": "SEAPLANE BASE", "airport_count": 104 }, { "fac_type": "HELIPORT", "airport_count": 30 } ] }, { "state": "NY", "airport_count": 576, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 393 }, { "fac_type": "HELIPORT", "airport_count": 156 }, { "fac_type": "SEAPLANE BASE", "airport_count": 23 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 }, { "fac_type": "GLIDERPORT", "airport_count": 2 } ] }, { "state": "WI", "airport_count": 543, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 439 }, { "fac_type": "HELIPORT", "airport_count": 85 }, { "fac_type": "SEAPLANE BASE", "airport_count": 16 }, { "fac_type": "ULTRALIGHT", "airport_count": 3 } ] } ]
WITH __stage0 AS ( SELECT group_set, base."state" as "state__0", CASE WHEN group_set=0 THEN COUNT(1) END as "airport_count__0", CASE WHEN group_set=1 THEN base."fac_type" END as "fac_type__1", CASE WHEN group_set=1 THEN COUNT(1) END as "airport_count__1" FROM '../data/airports.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1,2,4 ) , __stage1 AS ( SELECT *, CASE WHEN GROUP_SET=0 THEN ROW_NUMBER() OVER (PARTITION BY group_set ORDER BY "airport_count__0" desc) END as __row_number__0 FROM __stage0 ) , __stage2 AS ( SELECT *, MAX(CASE WHEN group_set IN (0,1) THEN CASE WHEN (GROUP_SET = 0 AND __row_number__0 > 10) THEN 1 ELSE 0 END END) OVER(PARTITION BY CAST("state__0" as VARCHAR)) as __shaving__0 FROM __stage1 WHERE 1=1 ) , __stage3 AS ( SELECT * FROM __stage2 WHERE NOT ((group_set IN (0,1) AND __shaving__0 > 0) ) ) SELECT "state__0" as "state", MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count", COALESCE(LIST({ "fac_type": "fac_type__1", "airport_count": "airport_count__1"} ORDER BY "airport_count__1" DESC NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_fac_type" FROM __stage3 GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 10
In such cases, the # dashboard renderer is useful for making the results easier to read:
# dashboard run: airports -> by_state_and_county
[ { "state": "TX", "airport_count": 1845, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 1389 }, { "fac_type": "HELIPORT", "airport_count": 435 }, { "fac_type": "ULTRALIGHT", "airport_count": 8 }, { "fac_type": "STOLPORT", "airport_count": 8 }, { "fac_type": "GLIDERPORT", "airport_count": 5 } ] }, { "state": "CA", "airport_count": 984, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 569 }, { "fac_type": "HELIPORT", "airport_count": 396 }, { "fac_type": "SEAPLANE BASE", "airport_count": 12 }, { "fac_type": "GLIDERPORT", "airport_count": 3 }, { "fac_type": "STOLPORT", "airport_count": 2 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 } ] }, { "state": "IL", "airport_count": 890, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 625 }, { "fac_type": "HELIPORT", "airport_count": 245 }, { "fac_type": "SEAPLANE BASE", "airport_count": 8 }, { "fac_type": "ULTRALIGHT", "airport_count": 6 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "BALLOONPORT", "airport_count": 2 }, { "fac_type": "STOLPORT", "airport_count": 2 } ] }, { "state": "FL", "airport_count": 856, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 511 }, { "fac_type": "HELIPORT", "airport_count": 280 }, { "fac_type": "SEAPLANE BASE", "airport_count": 43 }, { "fac_type": "STOLPORT", "airport_count": 13 }, { "fac_type": "ULTRALIGHT", "airport_count": 5 }, { "fac_type": "GLIDERPORT", "airport_count": 4 } ] }, { "state": "PA", "airport_count": 804, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 468 }, { "fac_type": "HELIPORT", "airport_count": 307 }, { "fac_type": "ULTRALIGHT", "airport_count": 13 }, { "fac_type": "SEAPLANE BASE", "airport_count": 10 }, { "fac_type": "GLIDERPORT", "airport_count": 3 }, { "fac_type": "STOLPORT", "airport_count": 3 } ] }, { "state": "OH", "airport_count": 749, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 537 }, { "fac_type": "HELIPORT", "airport_count": 201 }, { "fac_type": "STOLPORT", "airport_count": 4 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "SEAPLANE BASE", "airport_count": 2 }, { "fac_type": "BALLOONPORT", "airport_count": 1 } ] }, { "state": "IN", "airport_count": 643, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 497 }, { "fac_type": "HELIPORT", "airport_count": 115 }, { "fac_type": "ULTRALIGHT", "airport_count": 17 }, { "fac_type": "SEAPLANE BASE", "airport_count": 12 }, { "fac_type": "STOLPORT", "airport_count": 2 } ] }, { "state": "AK", "airport_count": 608, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 474 }, { "fac_type": "SEAPLANE BASE", "airport_count": 104 }, { "fac_type": "HELIPORT", "airport_count": 30 } ] }, { "state": "NY", "airport_count": 576, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 393 }, { "fac_type": "HELIPORT", "airport_count": 156 }, { "fac_type": "SEAPLANE BASE", "airport_count": 23 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 } ] }, { "state": "WI", "airport_count": 543, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 439 }, { "fac_type": "HELIPORT", "airport_count": 85 }, { "fac_type": "SEAPLANE BASE", "airport_count": 16 }, { "fac_type": "ULTRALIGHT", "airport_count": 3 } ] } ]
WITH __stage0 AS ( SELECT group_set, base."state" as "state__0", CASE WHEN group_set=0 THEN COUNT(1) END as "airport_count__0", CASE WHEN group_set=1 THEN base."fac_type" END as "fac_type__1", CASE WHEN group_set=1 THEN COUNT(1) END as "airport_count__1" FROM '../data/airports.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1,2,4 ) , __stage1 AS ( SELECT *, CASE WHEN GROUP_SET=0 THEN ROW_NUMBER() OVER (PARTITION BY group_set ORDER BY "airport_count__0" desc) END as __row_number__0 FROM __stage0 ) , __stage2 AS ( SELECT *, MAX(CASE WHEN group_set IN (0,1) THEN CASE WHEN (GROUP_SET = 0 AND __row_number__0 > 10) THEN 1 ELSE 0 END END) OVER(PARTITION BY CAST("state__0" as VARCHAR)) as __shaving__0 FROM __stage1 WHERE 1=1 ) , __stage3 AS ( SELECT * FROM __stage2 WHERE NOT ((group_set IN (0,1) AND __shaving__0 > 0) ) ) SELECT "state__0" as "state", MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count", COALESCE(LIST({ "fac_type": "fac_type__1", "airport_count": "airport_count__1"} ORDER BY "airport_count__1" DESC NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_fac_type" FROM __stage3 GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 10
Custom Labels
Use # label on aggregates or nested views to customize how they appear in the dashboard:
# dashboard run: airports -> { group_by: state # currency aggregate: # label='Total Airports' airport_count nest: # label='Facility Breakdown' by_fac_type is { group_by: fac_type aggregate: airport_count } limit: 5 }
[ { "state": "TX", "airport_count": 1845, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 1389 }, { "fac_type": "HELIPORT", "airport_count": 435 }, { "fac_type": "ULTRALIGHT", "airport_count": 8 }, { "fac_type": "STOLPORT", "airport_count": 8 }, { "fac_type": "GLIDERPORT", "airport_count": 5 } ] }, { "state": "CA", "airport_count": 984, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 569 }, { "fac_type": "HELIPORT", "airport_count": 396 }, { "fac_type": "SEAPLANE BASE", "airport_count": 12 }, { "fac_type": "GLIDERPORT", "airport_count": 3 }, { "fac_type": "STOLPORT", "airport_count": 2 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 } ] }, { "state": "IL", "airport_count": 890, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 625 }, { "fac_type": "HELIPORT", "airport_count": 245 }, { "fac_type": "SEAPLANE BASE", "airport_count": 8 }, { "fac_type": "ULTRALIGHT", "airport_count": 6 }, { "fac_type": "GLIDERPORT", "airport_count": 2 }, { "fac_type": "BALLOONPORT", "airport_count": 2 }, { "fac_type": "STOLPORT", "airport_count": 2 } ] }, { "state": "FL", "airport_count": 856, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 511 }, { "fac_type": "HELIPORT", "airport_count": 280 }, { "fac_type": "SEAPLANE BASE", "airport_count": 43 }, { "fac_type": "STOLPORT", "airport_count": 13 }, { "fac_type": "ULTRALIGHT", "airport_count": 5 }, { "fac_type": "GLIDERPORT", "airport_count": 4 } ] }, { "state": "PA", "airport_count": 804, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 468 }, { "fac_type": "HELIPORT", "airport_count": 307 }, { "fac_type": "ULTRALIGHT", "airport_count": 13 }, { "fac_type": "SEAPLANE BASE", "airport_count": 10 }, { "fac_type": "GLIDERPORT", "airport_count": 3 }, { "fac_type": "STOLPORT", "airport_count": 3 } ] } ]
WITH __stage0 AS ( SELECT group_set, base."state" as "state__0", CASE WHEN group_set=0 THEN COUNT(1) END as "airport_count__0", CASE WHEN group_set=1 THEN base."fac_type" END as "fac_type__1", CASE WHEN group_set=1 THEN COUNT(1) END as "airport_count__1" FROM '../data/airports.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1,2,4 ) , __stage1 AS ( SELECT *, CASE WHEN GROUP_SET=0 THEN ROW_NUMBER() OVER (PARTITION BY group_set ORDER BY "airport_count__0" desc) END as __row_number__0 FROM __stage0 ) , __stage2 AS ( SELECT *, MAX(CASE WHEN group_set IN (0,1) THEN CASE WHEN (GROUP_SET = 0 AND __row_number__0 > 5) THEN 1 ELSE 0 END END) OVER(PARTITION BY CAST("state__0" as VARCHAR)) as __shaving__0 FROM __stage1 WHERE 1=1 ) , __stage3 AS ( SELECT * FROM __stage2 WHERE NOT ((group_set IN (0,1) AND __shaving__0 > 0) ) ) SELECT "state__0" as "state", MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count", COALESCE(LIST({ "fac_type": "fac_type__1", "airport_count": "airport_count__1"} ORDER BY "airport_count__1" DESC NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_fac_type" FROM __stage3 GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 5
Layout Breaks
By default, nested views in a dashboard flow side by side. Use # break on a nested view to force it onto a new row:
# dashboard run: airports -> { group_by: state aggregate: airport_count nest: # break by_fac_type is { group_by: fac_type aggregate: airport_count } limit: 5 }
[ { "state": "TX", "airport_count": 1845, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 1389 }, { "fac_type": "HELIPORT", "airport_count": 435 }, { "fac_type": "STOLPORT", "airport_count": 8 }, { "fac_type": "ULTRALIGHT", "airport_count": 8 }, { "fac_type": "GLIDERPORT", "airport_count": 5 } ] }, { "state": "CA", "airport_count": 984, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 569 }, { "fac_type": "HELIPORT", "airport_count": 396 }, { "fac_type": "SEAPLANE BASE", "airport_count": 12 }, { "fac_type": "GLIDERPORT", "airport_count": 3 }, { "fac_type": "STOLPORT", "airport_count": 2 }, { "fac_type": "ULTRALIGHT", "airport_count": 2 } ] }, { "state": "IL", "airport_count": 890, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 625 }, { "fac_type": "HELIPORT", "airport_count": 245 }, { "fac_type": "SEAPLANE BASE", "airport_count": 8 }, { "fac_type": "ULTRALIGHT", "airport_count": 6 }, { "fac_type": "BALLOONPORT", "airport_count": 2 }, { "fac_type": "STOLPORT", "airport_count": 2 }, { "fac_type": "GLIDERPORT", "airport_count": 2 } ] }, { "state": "FL", "airport_count": 856, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 511 }, { "fac_type": "HELIPORT", "airport_count": 280 }, { "fac_type": "SEAPLANE BASE", "airport_count": 43 }, { "fac_type": "STOLPORT", "airport_count": 13 }, { "fac_type": "ULTRALIGHT", "airport_count": 5 }, { "fac_type": "GLIDERPORT", "airport_count": 4 } ] }, { "state": "PA", "airport_count": 804, "by_fac_type": [ { "fac_type": "AIRPORT", "airport_count": 468 }, { "fac_type": "HELIPORT", "airport_count": 307 }, { "fac_type": "ULTRALIGHT", "airport_count": 13 }, { "fac_type": "SEAPLANE BASE", "airport_count": 10 }, { "fac_type": "STOLPORT", "airport_count": 3 }, { "fac_type": "GLIDERPORT", "airport_count": 3 } ] } ]
WITH __stage0 AS ( SELECT group_set, base."state" as "state__0", CASE WHEN group_set=0 THEN COUNT(1) END as "airport_count__0", CASE WHEN group_set=1 THEN base."fac_type" END as "fac_type__1", CASE WHEN group_set=1 THEN COUNT(1) END as "airport_count__1" FROM '../data/airports.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1,2,4 ) , __stage1 AS ( SELECT *, CASE WHEN GROUP_SET=0 THEN ROW_NUMBER() OVER (PARTITION BY group_set ORDER BY "airport_count__0" desc) END as __row_number__0 FROM __stage0 ) , __stage2 AS ( SELECT *, MAX(CASE WHEN group_set IN (0,1) THEN CASE WHEN (GROUP_SET = 0 AND __row_number__0 > 5) THEN 1 ELSE 0 END END) OVER(PARTITION BY CAST("state__0" as VARCHAR)) as __shaving__0 FROM __stage1 WHERE 1=1 ) , __stage3 AS ( SELECT * FROM __stage2 WHERE NOT ((group_set IN (0,1) AND __shaving__0 > 0) ) ) SELECT "state__0" as "state", MAX(CASE WHEN group_set=0 THEN "airport_count__0" END) as "airport_count", COALESCE(LIST({ "fac_type": "fac_type__1", "airport_count": "airport_count__1"} ORDER BY "airport_count__1" DESC NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_fac_type" FROM __stage3 GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 5
Dashboards with Charts
Nested views inside a dashboard can use any visualization tag. This lets you combine KPI cards, tables, and charts in a single view:
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() }
# dashboard run: flights -> { group_by: carriers.nickname aggregate: flight_count nest: # big_value kpis is { aggregate: # label="Flights" flight_count } # break # bar_chart by_destination is { group_by: destination aggregate: flight_count limit: 10 } limit: 3 }
[ { "nickname": "Southwest", "flight_count": 88751, "kpis": { "flight_count": 88751 }, "by_destination": [ { "destination": "PHX", "flight_count": 6437 }, { "destination": "LAS", "flight_count": 6288 }, { "destination": "BWI", "flight_count": 5481 }, { "destination": "MDW", "flight_count": 4699 }, { "destination": "LAX", "flight_count": 4279 }, { "destination": "OAK", "flight_count": 3967 }, { "destination": "HOU", "flight_count": 3217 }, { "destination": "BNA", "flight_count": 3165 }, { "destination": "SAN", "flight_count": 2874 }, { "destination": "MCI", "flight_count": 2588 } ] }, { "nickname": "USAir", "flight_count": 37683, "kpis": { "flight_count": 37683 }, "by_destination": [ { "destination": "CLT", "flight_count": 6433 }, { "destination": "PHL", "flight_count": 5762 }, { "destination": "PIT", "flight_count": 4104 }, { "destination": "DCA", "flight_count": 2911 }, { "destination": "LGA", "flight_count": 1417 }, { "destination": "BOS", "flight_count": 1217 }, { "destination": "DFW", "flight_count": 657 }, { "destination": "RDU", "flight_count": 639 }, { "destination": "IAH", "flight_count": 604 }, { "destination": "ATL", "flight_count": 523 } ] }, { "nickname": "American", "flight_count": 34577, "kpis": { "flight_count": 34577 }, "by_destination": [ { "destination": "DFW", "flight_count": 8745 }, { "destination": "ORD", "flight_count": 5147 }, { "destination": "LAX", "flight_count": 1948 }, { "destination": "LGA", "flight_count": 1056 }, { "destination": "MIA", "flight_count": 1051 }, { "destination": "JFK", "flight_count": 949 }, { "destination": "STL", "flight_count": 714 }, { "destination": "SFO", "flight_count": 686 }, { "destination": "AUS", "flight_count": 671 }, { "destination": "BOS", "flight_count": 606 } ] } ]
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 COUNT(1) END as "flight_count__1", CASE WHEN group_set=2 THEN base."destination" END as "destination__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,5 ) , __stage1 AS ( SELECT *, CASE WHEN GROUP_SET=0 THEN ROW_NUMBER() OVER (PARTITION BY group_set ORDER BY "flight_count__0" desc) END as __row_number__0, CASE WHEN GROUP_SET=2 THEN ROW_NUMBER() OVER (PARTITION BY CAST("nickname__0" as VARCHAR), group_set ORDER BY "flight_count__2" desc) END as __row_number__2 FROM __stage0 ) , __stage2 AS ( SELECT *, MAX(CASE WHEN group_set IN (0,1,2) THEN CASE WHEN (GROUP_SET = 0 AND __row_number__0 > 3) THEN 1 ELSE 0 END END) OVER(PARTITION BY CAST("nickname__0" as VARCHAR)) as __shaving__0 FROM __stage1 WHERE NOT ((GROUP_SET = 2 AND __row_number__2 > 10)) ) , __stage3 AS ( SELECT * FROM __stage2 WHERE NOT ((group_set IN (0,1,2) AND __shaving__0 > 0) ) ) SELECT "nickname__0" as "nickname", MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count", COALESCE(FIRST({"flight_count": "flight_count__1" }) FILTER(WHERE group_set=1), {"flight_count": NULL}) as "kpis", COALESCE(LIST({ "destination": "destination__2", "flight_count": "flight_count__2"} ORDER BY "flight_count__2" DESC NULLS LAST) FILTER (WHERE group_set=2),[]) as "by_destination" FROM __stage3 GROUP BY 1 ORDER BY 2 desc NULLS LAST LIMIT 3