Query: activedirectory_deprecated_user_with_owner_status

with deprecated_account as (
  select
    distinct u.display_name,
    u.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 not u.account_enabled
),
deprecated_account_status as (
  select
    case
      when dp.id is not null then 'with owner role'
      else 'no owner role'
    end as deprecated_account_status
  from
    azuread_user as u
    left join deprecated_account as dp on u.id = dp.id
  where
    not u.account_enabled
)
select
  deprecated_account_status,
  count(*)
from
  deprecated_account_status
group by
  deprecated_account_status;