Query: identity_groups_with_policy

with group_with_policy as (
  select
    distinct g.name as group_name,
    g.id as group_id
  from
    oci_identity_policy as p,
    jsonb_array_elements_text(statements) as s,
    oci_identity_group as g
  where
    s ilike '%' || g.name || '%'
),
group_without_policy as (
  select
    id,
    case
      when id not in (
        select
          distinct group_id
        from
          group_with_policy
      ) then 'no policies'
      else 'with policies'
    end as has_policies
  from
    oci_identity_group
)
select
  has_policies,
  count(*)
from
  group_without_policy
group by
  has_policies;