Last active
February 14, 2019 13:22
-
-
Save simonbrandhof/b8ad390e1b93a25a2490fb5f09bd900e to your computer and use it in GitHub Desktop.
Issues Faceting
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment