Query: oci_vcn_by_rfc1918_range

with cidr_buckets as (
  select
    id,
    title,
    b,
    case
      when (b)::cidr <<= '10.0.0.0/16'::cidr then '10.0.0.0/16'
      when (b)::cidr <<= '172.16.0.0/16'::cidr then '172.16.0.0/16'
      when (b)::cidr <<= '192.168.0.0/16'::cidr then '192.168.0.0/16'
      else 'Public Range'
    end as rfc1918_bucket
  from
    oci_core_vcn,
    jsonb_array_elements_text(cidr_blocks) as b
  where
    lifecycle_state <> 'TERMINATED'
)
select
  rfc1918_bucket,
  count(*) as "VCNs"
from
  cidr_buckets
group by
  rfc1918_bucket
order by
  rfc1918_bucket;