Individual Velocity by Sprint

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.
Metric is based on the current state of the Assignee field. If you use special custom fields to track who worked on the ticket or you need to track the history of assignments, the metric's code has to be adjusted.
For N last sprints metric is the calculation of how many valid tickets & story points a particular employee has completed. Also, there is a way to track the number of invalid story points completed. Valid & invalid criteria are set in configuration based on resolutions.
A ticket has to be assigned to a sprint & completed by the sprint end date.

Configuration

Assignee. You need to set a specific employee's email here.
Number of Sprints to Show. A number of sprints are shown on the metric. Default: 6
Valid Resolutions. Set of resolutions that are considered valid. Default: Done, Closed, Fixed.

Code

with assignee as ( select 'jack_black@email.com' --employee's email
as assigned_to),

number_of_sprints_to_show as
(
select 6 --how many sprints to show on the chart
),

valid_resolutions as --set of valid resolutions
(
select 'Done' as r
union select 'Fixed' as r
union select 'Closed' as r
),

sprints as (
select
distinct s.id,
s.name,
s.start_date,
s.finish_date,
s.complete_date,
s.state
from sprint s
left join ticket t
on s.id = any(t.sprints)
where assigned_to = (select assigned_to from assignee)
and s.state != 'FUTURE'
order by
s.complete_date desc,
s.start_date desc,
s.state,
s.name
limit (select * from number_of_sprints_to_show)
),

completed_by_sprints as (
select
s.id as sprint_id,
s.name as sprint_name,
s.finish_date as finish_date,
t.key,
t.story_points,
t.resolution,
t.priority,
t.summary,
t.type
from sprint s
left join ticket t
on s.id = any(t.sprints)
and t.done_date <= coalesce(s.complete_date, s.finish_date)
where assigned_to = (select assigned_to from assignee)
),

completed_result as (
select
sprint_id,
sprint_name,
finish_date,
sum(case when resolution in (select * from valid_resolutions) or resolution is null then 1 else 0 end)::int as items,
sum(case when resolution in (select * from valid_resolutions) or resolution is null then story_points else 0 end) as story_points,
sum(case when resolution not in (select * from valid_resolutions) then story_points else 0 end) as invalid_sp
from completed_by_sprints
group by sprint_id , sprint_name, finish_date
)

select
s.name as "Sprint name",
coalesce(comp.items,0) as "Items completed",
coalesce(comp.story_points,0) as "Story points completed",
coalesce(comp.invalid_sp,0) as "Invalid Story points"
from
sprints s
left join
completed_result comp
on s.id = comp.sprint_id
order by
s.complete_date asc,
s.start_date asc,
s.state,
s.name

Drill-down

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

select
key, summary, type, priority, story_points
from (select
key,
summary,
type,
priority,
coalesce(story_points,0) as story_points,
sprint_name
from completed_by_sprints where (resolution in (select r from valid_resolutions) or resolution is null)) as a
where sprint_name = clicked_x_value
order by story_points desc, priority asc