PATTERN: UNION similar things to combine them

find-unused-security-groups.sql

with lambda_sgs as (
  select
    'Lambda' as service,
    f.arn,
    sg_id as security_group_id
  from
    aws_lambda_function as f,
    jsonb_array_elements_text(f.vpc_security_group_ids) as sg_id
),
ec2_instance_sgs as (
  select
    'EC2' as service,
    i.arn,
    sg ->> 'GroupId' as security_group_id
  from
    aws_ec2_instance as i,
    jsonb_array_elements(i.security_groups) as sg
),
all_sg_attachments as (
  select * from lambda_sgs
  union
  select * from ec2_instance_sgs
)
select
  sg.group_name,
  sg.group_id,
  count(sga.security_group_id)
from
  aws_vpc_security_group as sg
full outer join
  all_sg_attachments as sga on sg.group_id = sga.security_group_id
group by
  sg.group_name,
  sg.group_id
order by
  count desc