Query: vpc_by_rfc1918_range

with cidr_buckets as (
  select
    vpc_id,
    title,
    cidr_block as cidr,
    case
      when cidr_block << = '10.0.0.0/8' :: cidr then '10.0.0.0/8'
      when cidr_block << = '172.16.0.0/12' :: cidr then '172.16.0.0/12'
      when cidr_block << = '192.168.0.0/16' :: cidr then '192.168.0.0/16'
      else 'Public Range'
    end as rfc1918_bucket
  from
    alicloud_vpc
  union
  select
    vpc_id,
    title,
    b :: cidr as cidr,
    case
      when b :: cidr << = '10.0.0.0/8' :: cidr then '10.0.0.0/8'
      when b :: cidr << = '172.16.0.0/12' :: cidr then '172.16.0.0/12'
      when b :: cidr << = '192.168.0.0/16' :: cidr then '192.168.0.0/16'
      else 'Public Range'
    end as rfc1918_bucket
  from
    alicloud_vpc,
    jsonb_array_elements_text(secondary_cidr_blocks) as b
)
select
  rfc1918_bucket,
  count(*)
from
  cidr_buckets
group by
  rfc1918_bucket
order by
  rfc1918_bucket