Query: activedirectory_external_guest_user_with_owner_role_status

with external_guest_user_with_owner_role as (
  select
    distinct u.id,
    d.role_name,
    u.account_enabled,
    u.user_principal_name,
    d.subscription_id
  from
    azuread_user as u
    left join azure_role_assignment as a on a.principal_id = u.id
    left join azure_role_definition as d on d.id = a.role_definition_id
  where
    d.role_name = 'Owner'
    and (u.user_principal_name like '%EXT%' or user_type = 'Guest')
)
select
  case when u.id in (select  id  from external_guest_user_with_owner_role ) then 'with owner role' else 'no owner role' end as status,
  count(*)
from
  azuread_user as u
where
  u.user_principal_name like '%EXT%'
group by
  status;