Story Points Completed 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).
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
),
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.story_points, a.summary, a.type from Ticket 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 ticket 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 and a.story_points is not null
order by a.key desc
)
select author as "Developer",
sum(case when name=(select name from sprints where i=1) then story_points else 0 end) as "Current Sprint",
sum(case when name=(select name from sprints where i=2) then story_points else 0 end) as "Current Sprint-1",
sum(case when name=(select name from sprints where i=3) then story_points else 0 end) as "Current Sprint-2",
sum(case when name=(select name from sprints where i=4) then story_points else 0 end) as "Current Sprint-3",
sum(story_points)
from temp
group by "Developer"
order by sum(story_points) |
Drill-down query to show the ticket's attributes for a clicked developer/sprint:
select sprint, key, summary, type, story_points from
(select a.name as sprint, key, summary, type, story_points, author
from temp a
join sprints b on a.name=b.name
where i=4 ) as x
where author=clicked_x_value
order by story_points desc |
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 b.field=0 and b.status='Code Review' and a.done_date is not null and a.story_points is not null
and a.done_date>=now()-interval '13 week'
order by a.key desc
)
select author as "Developer",
sum(story_points)
from temp
group by "Developer"
order by sum(story_points) |
'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: