with links as
(
select key, count(bug) as count from
(
select a.key, b.key as bug from (
select key, jsonb_array_elements(outgoing_workitem_links) ->> 'name' AS link_type, type,
jsonb_array_elements(outgoing_workitem_links) ->> 'issueKey' AS link
from Ticket) AS a
join ticket b on a.link=b.key
where a.type='Story' and b.type='Bug'
union
select a.key, b.key as bug from (
select key, jsonb_array_elements(ingoing_workitem_links) ->> 'name' AS link_type, type,
jsonb_array_elements(ingoing_workitem_links) ->> 'issueKey' AS link
from Ticket) AS a
join ticket b on a.link=b.key
where a.type='Story' and b.type='Bug'
) as temp
group by key
),
temp as
(
select distinct a.key, a.summary, a.story_points, b.author, c.count
from Ticket a
join tickethistory b on a.id=b.workitem_id
left join links c on a.key=c.key
where b.field=0 and b.status='Code Review' and b.start>=now()-interval '13 week' and a.type in ('Story')
order by a.key desc
)
select author,
coalesce(sum(count)/sum(story_points),0) as "Defect Density"
from temp
group by author
order by coalesce(sum(count)/sum(story_points),0) asc |