Query: vpc_security_group_unassociated_status

with associated_sg as (
  select
    sg ->> 'GroupId' as sg_id,
    sg ->> 'GroupName' as sg_name
  from
    aws_ec2_network_interface,
    jsonb_array_elements(groups) as sg
),
sg_list as (
  select
    s.group_id,
    case
      when a.sg_id is null then false
      else true
    end as is_associated
  from
    aws_vpc_security_group as s
    left join associated_sg a on s.group_id = a.sg_id
)
select
  case
    when is_associated then 'associated'
    else 'unassociated'
  end as sg_association_status,
    count(*)
from
  sg_list
group by is_associated;