Defects Fixed by Developer
- Aliaksei Apet
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: