Query: ibm_is_vpc_subnets_for_vpc

with subnets as (
  select
    id,
    name,
    tags,
    ipv4_cidr_block,
    zone ->> 'name' as zone,
    available_ipv4_address_count,
    power(2, 32 - masklen(ipv4_cidr_block :: cidr)) -1 as raw_size
  from
    ibm_is_subnet
  where
    vpc ->> 'crn' = $1
)
select
  id as "Subnet ID",
  name as "Name",
  ipv4_cidr_block as "CIDR Block",
  zone as " Zone",
  available_ipv4_address_count as "Available IPs",
  power(2, 32 - masklen(ipv4_cidr_block :: cidr)) -1 as "Total IPs",
  round(
    100 * (available_ipv4_address_count / (raw_size)) :: numeric,
    2
  ) as "% Free"
from
  subnets
order by
  id;