malloy logo Malloy Documentation
search

Nested Queries

Nested queries, more formally known as "aggregating subqueries" are queries included in other queries. A nested query produces a subtable per row in the query in which it is embedded. In Malloy, queries can be named and referenced in other queries. The technical term "aggregating subquery" is a bit of a mouthful, so we more often refer to it as a "nested query."

When a named query is nested inside of another query, it produces an aggregating subquery and the results include a nested subtable.

query: airports -> {
  group_by: state
  aggregate: airport_count
  nest: by_facility is {
    group_by: fac_type
    aggregate: airport_count
  }
}
QUERY RESULTS
stateairport_​countby_​facility
TX1,845
fac_​typeairport_​count
AIRPORT1,389
HELIPORT435
ULTRALIGHT8
STOLPORT8
GLIDERPORT5
CA984
fac_​typeairport_​count
AIRPORT569
HELIPORT396
SEAPLANE BASE12
GLIDERPORT3
ULTRALIGHT2
STOLPORT2
IL890
fac_​typeairport_​count
AIRPORT625
HELIPORT245
SEAPLANE BASE8
ULTRALIGHT6
BALLOONPORT2
GLIDERPORT2
STOLPORT2
FL856
fac_​typeairport_​count
AIRPORT511
HELIPORT280
SEAPLANE BASE43
STOLPORT13
ULTRALIGHT5
GLIDERPORT4
PA804
fac_​typeairport_​count
AIRPORT468
HELIPORT307
ULTRALIGHT13
SEAPLANE BASE10
STOLPORT3
GLIDERPORT3
[
  {
    "state": "TX",
    "airport_count": 1845,
    "by_facility": [
      {
        "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_facility": [
      {
        "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_facility": [
      {
        "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": "GLIDERPORT",
        "airport_count": 2
      },
      {
        "fac_type": "STOLPORT",
        "airport_count": 2
      }
    ]
  },
  {
    "state": "FL",
    "airport_count": 856,
    "by_facility": [
      {
        "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_facility": [
      {
        "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,
    CASE WHEN group_set IN (0,1) THEN
      airports.state
      END as state__0,
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as airport_count__0,
    CASE WHEN group_set=1 THEN
      airports.fac_type
      END as fac_type__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,1,1)))
  GROUP BY 1,2,4
)
SELECT
  state__0 as state,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    fac_type__1 as fac_type, 
    airport_count__1 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc ) as by_facility
FROM __stage0
GROUP BY 1
ORDER BY 2 desc

Nesting Nested Queries

Aggregating subqueries can be nested infinitely, meaning that a nested query can contain another nested query.

query: airports -> {
  group_by: state
  aggregate: airport_count
  nest: top_5_counties is {
    top: 5
    group_by: county
    aggregate: airport_count
    nest: by_facility is {
      group_by: fac_type
      aggregate: airport_count
    }
  }
}
QUERY RESULTS
stateairport_​counttop_​5_​counties
TX1,845
countyairport_​countby_​facility
HARRIS135
fac_​typeairport_​count
HELIPORT110
AIRPORT25
TARRANT63
fac_​typeairport_​count
HELIPORT35
AIRPORT26
STOLPORT1
ULTRALIGHT1
DENTON53
fac_​typeairport_​count
AIRPORT47
HELIPORT6
DALLAS42
fac_​typeairport_​count
HELIPORT32
AIRPORT9
STOLPORT1
BEXAR40
fac_​typeairport_​count
AIRPORT24
HELIPORT16
CA984
countyairport_​countby_​facility
LOS ANGELES176
fac_​typeairport_​count
HELIPORT151
AIRPORT23
SEAPLANE BASE2
SAN BERNARDINO71
fac_​typeairport_​count
AIRPORT47
HELIPORT24
ORANGE53
fac_​typeairport_​count
HELIPORT47
AIRPORT6
SAN DIEGO49
fac_​typeairport_​count
AIRPORT30
HELIPORT17
GLIDERPORT2
KERN49
fac_​typeairport_​count
AIRPORT41
HELIPORT7
ULTRALIGHT1
IL890
countyairport_​countby_​facility
COOK51
fac_​typeairport_​count
HELIPORT44
AIRPORT7
LA SALLE39
fac_​typeairport_​count
AIRPORT35
HELIPORT4
MC HENRY29
fac_​typeairport_​count
AIRPORT20
HELIPORT7
SEAPLANE BASE2
DE KALB27
fac_​typeairport_​count
AIRPORT24
HELIPORT3
WINNEBAGO24
fac_​typeairport_​count
AIRPORT15
HELIPORT9
FL856
countyairport_​countby_​facility
PALM BEACH45
fac_​typeairport_​count
HELIPORT30
AIRPORT14
GLIDERPORT1
DADE44
fac_​typeairport_​count
HELIPORT27
AIRPORT12
GLIDERPORT2
SEAPLANE BASE2
STOLPORT1
POLK43
fac_​typeairport_​count
AIRPORT18
HELIPORT16
SEAPLANE BASE9
MARION37
fac_​typeairport_​count
AIRPORT27
HELIPORT7
SEAPLANE BASE2
STOLPORT1
ORANGE36
fac_​typeairport_​count
HELIPORT24
AIRPORT8
SEAPLANE BASE4
PA804
countyairport_​countby_​facility
BUCKS55
fac_​typeairport_​count
AIRPORT32
HELIPORT19
ULTRALIGHT2
STOLPORT1
GLIDERPORT1
MONTGOMERY44
fac_​typeairport_​count
HELIPORT29
AIRPORT14
SEAPLANE BASE1
ALLEGHENY31
fac_​typeairport_​count
HELIPORT22
AIRPORT8
SEAPLANE BASE1
CHESTER27
fac_​typeairport_​count
HELIPORT16
AIRPORT11
PHILADELPHIA26
fac_​typeairport_​count
HELIPORT22
AIRPORT4
[
  {
    "state": "TX",
    "airport_count": 1845,
    "top_5_counties": [
      {
        "county": "HARRIS",
        "airport_count": 135,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 110
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 25
          }
        ]
      },
      {
        "county": "TARRANT",
        "airport_count": 63,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 35
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 26
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "DENTON",
        "airport_count": 53,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 47
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 6
          }
        ]
      },
      {
        "county": "DALLAS",
        "airport_count": 42,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 32
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 9
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "BEXAR",
        "airport_count": 40,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 24
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 16
          }
        ]
      }
    ]
  },
  {
    "state": "CA",
    "airport_count": 984,
    "top_5_counties": [
      {
        "county": "LOS ANGELES",
        "airport_count": 176,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 151
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 23
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 2
          }
        ]
      },
      {
        "county": "SAN BERNARDINO",
        "airport_count": 71,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 47
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 24
          }
        ]
      },
      {
        "county": "ORANGE",
        "airport_count": 53,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 47
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 6
          }
        ]
      },
      {
        "county": "SAN DIEGO",
        "airport_count": 49,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 30
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 17
          },
          {
            "fac_type": "GLIDERPORT",
            "airport_count": 2
          }
        ]
      },
      {
        "county": "KERN",
        "airport_count": 49,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 41
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 7
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 1
          }
        ]
      }
    ]
  },
  {
    "state": "IL",
    "airport_count": 890,
    "top_5_counties": [
      {
        "county": "COOK",
        "airport_count": 51,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 44
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 7
          }
        ]
      },
      {
        "county": "LA SALLE",
        "airport_count": 39,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 35
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 4
          }
        ]
      },
      {
        "county": "MC HENRY",
        "airport_count": 29,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 20
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 7
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 2
          }
        ]
      },
      {
        "county": "DE KALB",
        "airport_count": 27,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 24
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 3
          }
        ]
      },
      {
        "county": "WINNEBAGO",
        "airport_count": 24,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 15
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 9
          }
        ]
      }
    ]
  },
  {
    "state": "FL",
    "airport_count": 856,
    "top_5_counties": [
      {
        "county": "PALM BEACH",
        "airport_count": 45,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 30
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 14
          },
          {
            "fac_type": "GLIDERPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "DADE",
        "airport_count": 44,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 27
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 12
          },
          {
            "fac_type": "GLIDERPORT",
            "airport_count": 2
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 2
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "POLK",
        "airport_count": 43,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 18
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 16
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 9
          }
        ]
      },
      {
        "county": "MARION",
        "airport_count": 37,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 27
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 7
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 2
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "ORANGE",
        "airport_count": 36,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 24
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 8
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 4
          }
        ]
      }
    ]
  },
  {
    "state": "PA",
    "airport_count": 804,
    "top_5_counties": [
      {
        "county": "BUCKS",
        "airport_count": 55,
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 32
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 19
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 2
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          },
          {
            "fac_type": "GLIDERPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "MONTGOMERY",
        "airport_count": 44,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 29
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 14
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "ALLEGHENY",
        "airport_count": 31,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 22
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 8
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "CHESTER",
        "airport_count": 27,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 16
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 11
          }
        ]
      },
      {
        "county": "PHILADELPHIA",
        "airport_count": 26,
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 22
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 4
          }
        ]
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set IN (0,1,2) THEN
      airports.state
      END as state__0,
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as airport_count__0,
    CASE WHEN group_set IN (1,2) THEN
      airports.county
      END as county__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1,
    CASE WHEN group_set=2 THEN
      airports.fac_type
      END as fac_type__2,
    CASE WHEN group_set=2 THEN
      COUNT( 1)
      END as airport_count__2
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,2,1)))
  GROUP BY 1,2,4,6
)
, __stage1 AS (
  SELECT 
    CASE WHEN group_set=2 THEN 1  ELSE group_set END as group_set,
    state__0 as state__0,
    ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count__0,
    CASE WHEN group_set IN (1,2) THEN
      county__1
      END as county__1,
    ANY_VALUE(CASE WHEN group_set=1 THEN airport_count__1 END) as airport_count__1,
    ARRAY_AGG(CASE WHEN group_set=2 THEN STRUCT(
      fac_type__2 as fac_type, 
      airport_count__2 as airport_count
      ) END IGNORE NULLS  ORDER BY  airport_count__2 desc ) as by_facility__1
  FROM __stage0
  GROUP BY 1,2,4
)
SELECT
  state__0 as state,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    county__1 as county, 
    airport_count__1 as airport_count, 
    by_facility__1 as by_facility
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc  LIMIT 5) as top_5_counties
FROM __stage1
GROUP BY 1
ORDER BY 2 desc

Filtering Nested Queries

Filters can be applied at any level within nested queries.

query: airports -> {
  where: state ? 'CA' | 'NY' | 'MN'
  group_by: state
  aggregate: airport_count
  nest: top_5_counties is {
    top: 5
    group_by: county
    aggregate: airport_count
    nest: major_facilities is {
      where: major = 'Y'
      group_by: name is concat(code, ' - ', full_name)
    }
    nest: by_facility is {
      group_by: fac_type
      aggregate: airport_count
    }
  }
}
QUERY RESULTS
stateairport_​counttop_​5_​counties
CA984
countyairport_​countmajor_​facilitiesby_​facility
LOS ANGELES176
name
BUR - BURBANK-GLENDALE-PASADENA
LAX - LOS ANGELES INTL
LGB - LONG BEACH /DAUGHERTY FIELD/
fac_​typeairport_​count
HELIPORT151
AIRPORT23
SEAPLANE BASE2
SAN BERNARDINO71
name
ONT - ONTARIO INTL
fac_​typeairport_​count
AIRPORT47
HELIPORT24
ORANGE53
name
SNA - JOHN WAYNE AIRPORT-ORANGE COUNTY
fac_​typeairport_​count
HELIPORT47
AIRPORT6
SAN DIEGO49
name
SAN - SAN DIEGO INTL-LINDBERGH FLD
fac_​typeairport_​count
AIRPORT30
HELIPORT17
GLIDERPORT2
KERN49
name
BFL - MEADOWS FIELD
IYK - INYOKERN
fac_​typeairport_​count
AIRPORT41
HELIPORT7
ULTRALIGHT1
NY576
countyairport_​countmajor_​facilitiesby_​facility
SUFFOLK34
name
ISP - LONG ISLAND MAC ARTHUR
fac_​typeairport_​count
HELIPORT18
AIRPORT15
SEAPLANE BASE1
ERIE26
name
BUF - BUFFALO NIAGARA INTL
fac_​typeairport_​count
AIRPORT18
HELIPORT8
NIAGARA20
name
fac_​typeairport_​count
AIRPORT15
HELIPORT5
DUTCHESS20
name
fac_​typeairport_​count
HELIPORT12
AIRPORT8
ONEIDA18
name
fac_​typeairport_​count
AIRPORT13
HELIPORT5
MN507
countyairport_​countmajor_​facilitiesby_​facility
ST LOUIS28
name
DLH - DULUTH INTL
fac_​typeairport_​count
AIRPORT13
SEAPLANE BASE10
HELIPORT5
HENNEPIN23
name
MSP - MINNEAPOLIS-ST PAUL INTL/WOLD-CHAMBERLAIN/
fac_​typeairport_​count
HELIPORT9
SEAPLANE BASE8
AIRPORT6
DAKOTA17
name
fac_​typeairport_​count
AIRPORT14
SEAPLANE BASE3
CROW WING17
name
fac_​typeairport_​count
AIRPORT8
SEAPLANE BASE6
HELIPORT1
ULTRALIGHT1
STOLPORT1
ANOKA14
name
fac_​typeairport_​count
AIRPORT9
SEAPLANE BASE3
HELIPORT2
[
  {
    "state": "CA",
    "airport_count": 984,
    "top_5_counties": [
      {
        "county": "LOS ANGELES",
        "airport_count": 176,
        "major_facilities": [
          {
            "name": "BUR - BURBANK-GLENDALE-PASADENA"
          },
          {
            "name": "LAX - LOS ANGELES INTL"
          },
          {
            "name": "LGB - LONG BEACH /DAUGHERTY FIELD/"
          }
        ],
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 151
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 23
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 2
          }
        ]
      },
      {
        "county": "SAN BERNARDINO",
        "airport_count": 71,
        "major_facilities": [
          {
            "name": "ONT - ONTARIO INTL"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 47
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 24
          }
        ]
      },
      {
        "county": "ORANGE",
        "airport_count": 53,
        "major_facilities": [
          {
            "name": "SNA - JOHN WAYNE AIRPORT-ORANGE COUNTY"
          }
        ],
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 47
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 6
          }
        ]
      },
      {
        "county": "SAN DIEGO",
        "airport_count": 49,
        "major_facilities": [
          {
            "name": "SAN - SAN DIEGO INTL-LINDBERGH FLD"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 30
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 17
          },
          {
            "fac_type": "GLIDERPORT",
            "airport_count": 2
          }
        ]
      },
      {
        "county": "KERN",
        "airport_count": 49,
        "major_facilities": [
          {
            "name": "BFL - MEADOWS FIELD"
          },
          {
            "name": "IYK - INYOKERN"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 41
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 7
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 1
          }
        ]
      }
    ]
  },
  {
    "state": "NY",
    "airport_count": 576,
    "top_5_counties": [
      {
        "county": "SUFFOLK",
        "airport_count": 34,
        "major_facilities": [
          {
            "name": "ISP - LONG ISLAND MAC ARTHUR"
          }
        ],
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 18
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 15
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "ERIE",
        "airport_count": 26,
        "major_facilities": [
          {
            "name": "BUF - BUFFALO NIAGARA INTL"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 18
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 8
          }
        ]
      },
      {
        "county": "NIAGARA",
        "airport_count": 20,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 15
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 5
          }
        ]
      },
      {
        "county": "DUTCHESS",
        "airport_count": 20,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 12
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 8
          }
        ]
      },
      {
        "county": "ONEIDA",
        "airport_count": 18,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 13
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 5
          }
        ]
      }
    ]
  },
  {
    "state": "MN",
    "airport_count": 507,
    "top_5_counties": [
      {
        "county": "ST LOUIS",
        "airport_count": 28,
        "major_facilities": [
          {
            "name": "DLH - DULUTH INTL"
          }
        ],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 13
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 10
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 5
          }
        ]
      },
      {
        "county": "HENNEPIN",
        "airport_count": 23,
        "major_facilities": [
          {
            "name": "MSP - MINNEAPOLIS-ST PAUL INTL/WOLD-CHAMBERLAIN/"
          }
        ],
        "by_facility": [
          {
            "fac_type": "HELIPORT",
            "airport_count": 9
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 8
          },
          {
            "fac_type": "AIRPORT",
            "airport_count": 6
          }
        ]
      },
      {
        "county": "DAKOTA",
        "airport_count": 17,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 14
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 3
          }
        ]
      },
      {
        "county": "CROW WING",
        "airport_count": 17,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 8
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 6
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 1
          },
          {
            "fac_type": "ULTRALIGHT",
            "airport_count": 1
          },
          {
            "fac_type": "STOLPORT",
            "airport_count": 1
          }
        ]
      },
      {
        "county": "ANOKA",
        "airport_count": 14,
        "major_facilities": [],
        "by_facility": [
          {
            "fac_type": "AIRPORT",
            "airport_count": 9
          },
          {
            "fac_type": "SEAPLANE BASE",
            "airport_count": 3
          },
          {
            "fac_type": "HELIPORT",
            "airport_count": 2
          }
        ]
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set IN (0,1,2,3) THEN
      airports.state
      END as state__0,
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as airport_count__0,
    CASE WHEN group_set IN (1,2,3) THEN
      airports.county
      END as county__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1,
    CASE WHEN group_set=2 THEN
      concat(airports.code,' - ',airports.full_name)
      END as name__2,
    CASE WHEN group_set=3 THEN
      airports.fac_type
      END as fac_type__3,
    CASE WHEN group_set=3 THEN
      COUNT( 1)
      END as airport_count__3
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,3,1)))
  WHERE ((airports.state='CA')or((airports.state='NY')or(airports.state='MN')))
  AND ((group_set NOT IN (2) OR (group_set IN (2) AND airports.major='Y')))
  GROUP BY 1,2,4,6,7
)
, __stage1 AS (
  SELECT 
    CASE WHEN group_set=2 THEN 1 WHEN group_set=3 THEN 1  ELSE group_set END as group_set,
    state__0 as state__0,
    ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count__0,
    CASE WHEN group_set IN (1,2,3) THEN
      county__1
      END as county__1,
    ANY_VALUE(CASE WHEN group_set=1 THEN airport_count__1 END) as airport_count__1,
    ARRAY_AGG(CASE WHEN group_set=2 THEN STRUCT(
      name__2 as name
      ) END IGNORE NULLS  ORDER BY  name__2 asc ) as major_facilities__1,
    ARRAY_AGG(CASE WHEN group_set=3 THEN STRUCT(
      fac_type__3 as fac_type, 
      airport_count__3 as airport_count
      ) END IGNORE NULLS  ORDER BY  airport_count__3 desc ) as by_facility__1
  FROM __stage0
  GROUP BY 1,2,4
)
SELECT
  state__0 as state,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    county__1 as county, 
    airport_count__1 as airport_count, 
    major_facilities__1 as major_facilities, 
    by_facility__1 as by_facility
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc  LIMIT 5) as top_5_counties
FROM __stage1
GROUP BY 1
ORDER BY 2 desc