Query: vpc_in_more_than_one_region

with vpc_region_list as (
  select
    distinct region,
    account_id
  from
    aws_vpc
),
vpc_count_in_account as (
  select
    count(*) as num,
    account_id
  from
    vpc_region_list
  group by
    account_id
)
select
  arn as resource,
  case
    when v.num > 1 then 'ok'
    when v.num = 1 then 'alarm'
    else 'alarm'
  end as status,
  case
    when v.num > 1 then 'VPCs exist in ' || v.num || ' regions.'
    when v.num = 1 then 'VPCs exist only in one region.'
    else 'VPC does not exist.'
  end as reason,
  a.region,
  a.account_id
from
  aws_account as a
  left join vpc_count_in_account as v on v.account_id = a.account_id;