Defects Fixed by Developer

1 Description
2 By Sprint metric
3 Within the last N weeks metric

Description

This metric is based on Task Tracking System's (Jira, Azure Boards, or Rally) data.
A developer is taken from the record of transition to a particular state ('Code Review' status by default) - a developer is the one who has physically put the ticket to 'Code Review' status. Status is configurable.
There are two variations of metric - by sprints & within the last N weeks (13 weeks by default).
Defect criteria can be set in a metric's code. The default: Bug & Incident issue types.
Sprint metric takes the latest of assigned sprints for a completed ticket with story points.
Sprint metric provides 4 sprints - current, current-1 (previous sprint), current-2 & current-3 sprints.

By Sprint metric

with status as
(
  select 'Code Review' as s --status
),
 
defects as
(
  select * from ticket
  where type in ('Bug','Incident','Defect')
),
 
sprints as
(
select *,row_number() over (order by start_date desc) as i from sprint
where state in ('CLOSED', 'ACTIVE')
order by start_date desc
limit 4
  ),
 
temp as (
select distinct a.key, b.author, s.name, a.priority, a.summary, a.type 
  from defects a
join tickethistory b on a.id=b.workitem_id
join sprints s on s.id=(select max(x) from (select unnest(sprints) as x from defects where key=a.key) as xxx)
where b.field=0 and lower(b.status)=(select lower(s) from status) and a.done_date is not null
order by a.key desc
  )
   
select author as "Developer",
sum(case when name=(select name from sprints where i=1) then 1 else 0 end) as "Current Sprint",
sum(case when name=(select name from sprints where i=2) then 1 else 0 end) as "Current Sprint-1",
sum(case when name=(select name from sprints where i=3) then 1 else 0 end) as "Current Sprint-2",
sum(case when name=(select name from sprints where i=4) then 1 else 0 end) as "Current Sprint-3",
count(*)
from temp
group by "Developer"
order by count(*)

Drill-down query to show the ticket's attributes for a clicked developer/sprint:

select sprint, key, summary, type, priority from
(select a.name as sprint, key, summary, type, priority, author
from temp a
join sprints b on a.name=b.name
where i=4 ) as x
where author=clicked_x_value
order by priority asc

Adjust i variable for each of the series:

i=1 - current sprint
i=2 - current sprint-1
i=3 - current sprint-2
i=4 - current sprint-3

Within the last N weeks metric

with temp as (
select distinct a.key, b.author, a.story_points, a.summary, a.type, a.priority from Ticket a
join tickethistory b on a.id=b.workitem_id
where a.type='Bug' and b.field=0 and b.status='Code Review'
and a.done_date>=now()-interval '13 week'
order by a.key desc
)

select author as "Developer",
count(key)
from temp
group by "Developer"
order by count(key)

'Bug' type, 'Code Review' status & '13 week' interval can be adjusted based on your needs.

Drill-down query to show the ticket's attributes for a clicked developer: