Query: compute_virtual_machine_by_attachment_to_network

with vm_with_network_interfaces as (
  select
    vm.id as vm_id,
    n ->> 'id' as network_id
  from
    azure_compute_virtual_machine as vm,
    jsonb_array_elements(network_interfaces) as n
),
vm_with_appoved_networks as (
  select
    vn.vm_id as vm_id,
    vn.network_id as network_id,
    t.title as title
  from
    vm_with_network_interfaces as vn
    left join azure_network_interface as t on t.id = vn.network_id
  where
    exists (
      select
        ip -> 'properties' -> 'subnet' ->> 'id' as ip
      from
        azure_network_interface,
        jsonb_array_elements(ip_configurations) as ip
      where
        ip -> 'properties' -> 'subnet' ->> 'id' is not null
    )
)
select
  attachment,
  count(*)
from
  (
    select
      b.vm_id,
      case
        when b.vm_id is null then 'unattached'
        else 'attached'
      end attachment
    from
      azure_compute_virtual_machine as a
      left join vm_with_appoved_networks as b on a.id = b.vm_id
  ) as vm
group by
  attachment
order by
  attachment;