select g, severity, assignee, issueType, resolution, val, debt -- restrict limits to hotspots
from (
select g, severity, assignee, issueType, resolution, val, debt, row_number() over(
            partition by g
            order by val desc
        ) as rn
from (
select g,
       severity,
       assignee,
       issueType,
       resolution,
       case when g=7 then severityFacet
            when g=11 then unstickyFacet
            when g=13 then issueTypeFacet
            when g=14 then unstickyFacet
            when g=15 then total
            end val,
       debt
from (
  select grouping (severity, assignee, issueType, resolution) as g,
       severity,
       assignee,
       issueType,
       resolution,
       count(CASE when issueTypeFilter = 1 and languageFilter = 1 THEN 1 end) as severityFacet,
       count(CASE when issueTypeFilter = 1 and severityFilter = 1 and languageFilter = 1 THEN 1 end) as unstickyFacet,
       count(CASE when severityFilter = 1 and languageFilter = 1 THEN 1 end) as issueTypeFacet,
       count(*) as total,
       sum(effort) as debt
  from
  (select
       i.issue_type as issueType,
       i.severity as severity,
       i.assignee as assignee,
       i.effort as effort,
       i.resolution as resolution,
       case when i.issue_type in (1, 2, 3) then 1 ELSE 0 END issueTypeFilter,
       case when r.language = 'java' then 1 ELSE 0 END languageFilter,
       case when i.severity in ('BLOCKER', 'CRITICAL') then 1 ELSE 0 END severityFilter
    from issues i
    inner join rules r on r.id = i.rule_id
    inner join projects p on p.uuid = i.project_uuid
    where
      i.status != 'CLOSED' and
      p.organization_uuid = (SELECT text_value FROM internal_properties where kee = 'organization.default') and
      p.id in (
      select ur.resource_id
         from user_roles ur
        where ur.role='user' and ur.user_id=(select id from users where login='simonbrandhof@github')
        union
        select gr.resource_id
          from group_roles gr
          inner join groups_users gu on gu.group_id = gr.group_id and gu.user_id = (select id from users where login='simonbrandhof@github')
          where gr.role = 'user'
        union
        select id from projects where qualifier='TRK' and private=false
      )
  ) issues
  group by grouping sets (
    (),
    (severity),
    (assignee),
    (issueType),
    (resolution)
  )
) t
order by g, val desc
) t
) t
where (g = 7) or (g = 11 and rn <= 10) or (g = 13 and rn <= 5) or (g = 14 and rn <= 5) or (g = 15)