Defect Density per Story Point per Developer

1 Description
2 Configuration
3 Code
4 Drill-down

Description

This metric is based on Task Tracking System's (Jira, Azure Boards, or Rally) data.
A developer is a user who transitions a ticket to a particular state (Code Review status in this example).
Metric takes all stories moved to Code Review within the last 13 weeks (1 quarter) and counts how many defects were linked to that story (any types of ingoing and outgoing links).
Defect density = sum of story points in stories / number of defects linked to them.

Configuration

Only stories are taken by default (type='Story') - you can adjust/add more issue types if needed.
Defect types also can be adjusted - type='Bug' by default.
Time interval also can be updated (interval '13 week' by default).
The status for identifying a developer can be changed from Code Review to more appropriate in your case (status='Code Review').
Link type (link_type) between stories & defects can be set specifically.

Code

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

Drill-down

Drill-down query to show details for a clicked developer:

select key, summary, story_points, count as "# of defects per story"
from
(select key, summary, story_points, count, author from temp) as x
where author=clicked_x_value
order by count asc