An SQL script to split the UK into North and South by it's population in CARTO

north_south_divide.sql

CREATE OR REPLACE TABLE
  `dataset.north_south_divide`
CLUSTER BY
  h3 AS(
  WITH
    england_grid AS (
    SELECT
      geoid AS h3,
      population
    FROM
      `h3_population_grid` pop
    JOIN
      england_boundary bound
    ON
      ST_INTERSECTS(bound.geom, `carto-un`.carto.H3_CENTER(pop.geoid) )),
    total_pop AS (
    SELECT
      SUM(population) AS tot_pop
    FROM
      england_grid ),
    cumulative_sum AS (
    SELECT
      h3,
      -- * EXCEPT(geoid),
      population,
      SUM(population) OVER (ORDER BY ST_Y(`carto-un`.carto.H3_CENTER(h3)) DESC) AS cumulative_pop
    FROM
      england_grid
    ORDER BY
      ST_Y(`carto-un`.carto.H3_CENTER(h3)))
  SELECT
    h3,
    cumulative_sum.population,
    cumulative_pop,
    CASE
      WHEN cumulative_pop >= tot_pop/2 THEN 'south'
    ELSE
    'north'
  END
    AS north_south
  FROM
    cumulative_sum,
    total_pop )