Commitment rate by sprints/releases
- Aleksandr (Unlicensed)
This article describes what “Commitment rate by sprints/releases” metric is and how it helps and works
Creating a metric
See Custom Metric development for details
Creating a Custom Selection
See Custom Metric development to see how to get to a Custom Selection mode.
Further will be the explanation of the code you should put in the “PerfQL” field
Including/Excluding sub-items and limitation of rows in the result set
First of all we have to decide if we want to include sub-items in our calculations or not. To do so we should type “1” in “include_sub_items“ query in case if we want to exclude them. Otherwise type any other number.
In order to limit rows in the result set we should type exact number of rows we want to get. For this purpose we have “limit_rows” query where the “number_of_rows“ column can be changed .
with
include_sub_items as (
select 1 -- 1 - exclude sub-items, any other number - include sub-items
as y_n
),
limit_rows as (
select 12 as number_of_rows -- change the number after "select" to
-- change the amount of sprints in the result
)Committed part
Gathering history of all required tickets
To gather all ticket related history from two tables - “ticket“ and “tickethistory“, we need to retrieve data about ticket types from “tickethistory” table at first (where “field“ column values equal to 11). Then we join types and tickethistory (where “field“ column values equal to 1 for sprint or 2 for releases). If some tickets are absent in “tickethistory” table we need to retrieve required data from “ticket” table. To do so we join tickets we have got on the previous step with “ticket” table and leave only those rows where a ticket id from “tickethistory” table is null. Then we use union operation to combine them together.
ticket_history_types as (
select
th.workitem_id,
th.type,
th.start,
max(th.start) over (partition by th.workitem_id) as last_type_date
from
tickethistory th
where
th.field = 11
),
ticket_history_keys as (
select
th.workitem_id as wrktm_id,
th.sprint as spr,
th.start as sdt
from
ticket_history_types t
join
tickethistory th
on t.workitem_id = th.workitem_id
where ((select y_n from include_sub_items) <> 1 or (lower(t.type) not like 'sub%'))
and th.field = 1
),
ticket_keys as (
select
t.id as wrktm_id,
t.key,
t.story_points as sp,
array_to_string(t.sprints, ',') as spr,
t.created as sdt
from
ticket t
left join
ticket_history_keys thk
on t.id = thk.wrktm_id
where thk.wrktm_id is null
and ((select y_n from include_sub_items) <> 1 or (lower(t.type) not like 'sub%' and t.parent_task is null ))
),
t_h_all as (
select th_keys.wrktm_id, th_keys.spr, th_keys.sdt
from ticket_history_keys th_keys
union all
select tk.wrktm_id, tk.spr as spr, tk.sdt
from ticket_keys tk
)Example of the output:
Numbering records with sprints and finding dates of the next records
In order to define borders of commit-period correctly we previously need to assign a number to a record with sprints/releases. This number is the position of the record in the sequence of sprint/release records in “Tickethistory” table for current ticket id (rang column). Also we select start date of the next row for every current row within particular ticket id to define the period of time the record was actual for (next_sdt column).
keys_sprints_rangs_dates as (
select
a.wrktm_id,
coalesce(a.spr, 'no sprint') as sprint,
row_number() over (partition by a.wrktm_id order by a.sdt) as rang,
a.sdt as sdt,
lead(a.sdt) over (partition by a.wrktm_id order by a.sdt) as next_sdt
from t_h_all a
)Example of the output:
Turning rows with sprints/releases into columns
To define commit-period for each sprint separately we must split every row into a column. To do so we use function regexp_split_to_table(sprint, ',').
table_sprints as (
select
wrktm_id,
rang,
regexp_split_to_table(sprint, ',') as sprint,
sdt,
next_sdt
from keys_sprints_rangs_dates
)Example of the output:
Gone sprints
Now we need to define sprints/releases the ticket was deassigned from for every record we have numbered before. To do so we need to join the set of sprits/releases for current rang with the set of sprints/releases for the next rang and extract the subset of sprints/releases that we have in current set only. That’s why we make self-join of “table_sprints“ table by equality of workitem ids and sprints. To join current record and the next record we use the following condition: a.rang = b.rang - 1. To extract the required subset of sprints/releases that is absent in the next set we use “where“ condition to choose only those sprints/releases which have no pair in the next set.
gone_sprints as ( --ticket gone from sprint
select
a.wrktm_id,
a.sprint,
a.next_sdt,
a.rang
from
table_sprints a
left join
table_sprints b
on a.wrktm_id = b.wrktm_id
and a.rang = b.rang - 1
and a.sprint = b.sprint
where b.sprint is null
)Example of the output:
Added sprints
Now we need to make almost the same query as the previous but we are going to find out what sprints/releases the ticket was added to. To do so we make self join of “table_sprints“ table by equality of workitem ids and sprints/releases. To join current record and the previous record we use the following condition: a.rang = b.rang + 1.
added_sprints as ( --ticket came to sprint
select
a.wrktm_id,
a.sprint,
a.sdt,
a.rang
from
table_sprints a
left join
table_sprints b
on a.wrktm_id = b.wrktm_id
and a.rang = b.rang + 1
and a.sprint = b.sprint
where b.sprint is null
)Example of the output:
Sprint/release active dates
Here we gather all information we get on previous steps: for every record with several sprints/releases we have a period of time when ticket was assigned to particular sprint/release.
sprint_active_dates as (
select
ksrd.wrktm_id,
ksrd.rang,
ksrd.sprint,
ads.sprint as added_sprint,
ads.sdt as added_on,
gs.next_sdt as gone_on
from keys_sprints_rangs_dates ksrd
left join added_sprints ads
on ksrd.wrktm_id = ads.wrktm_id
and ksrd.rang = ads.rang
left join gone_sprints gs
on ksrd.wrktm_id = gs.wrktm_id
and ads.sprint = gs.sprint
and ads.rang = gs.rang
)Example of the output:
Story points
Now we need to find out how many story points were committed for every sprint/release. To do so we summarize story points of all committed to the sprint/release tickets that have maximum change date but were changed earlier than sprint/release began.
story_points_th as (
select
s.id,
th.workitem_id,
th.start,
th.story_points,
max(th.start) over (partition by th.workitem_id, s.id) as max_sp_dt
from sprint s
left join tickethistory th
on th.start <= coalesce(s.start_date, s.activated_date)
where th.field = 7
),
ticket_story_points as (
select
unnest(t.sprints) as sprints,
t.id,
coalesce(t.done_date, t.created) as st_dt,
t.story_points
from ticket t
left join story_points_th spth
on t.id = spth.workitem_id
where spth.workitem_id is null
),
story_points_comm_result as (
select
spth.id,
spth.workitem_id,
spth.start,
spth.story_points
from story_points_th spth
where spth.start = max_sp_dt
union all
select
ticket_story_points.sprints,
ticket_story_points.id,
ticket_story_points.st_dt,
ticket_story_points.story_points
from ticket_story_points
)Example of the output:
Original hours
Our purpose here is the same as we had on the previous step but here we retrieve original hours related data.
original_hours_th as (
select
s.id,
th.workitem_id,
th.start,
th.time_estimate,
max(th.start) over (partition by th.workitem_id, s.id) as max_oh_dt
from sprint s
left join tickethistory th
on th.start <= coalesce(s.start_date, s.activated_date)
where th.field = 4
),
ticket_orig_hours as (
select
unnest(t.sprints) as sprints,
t.id,
coalesce(t.done_date, t.created) as st_dt,
t.time_estimate
from ticket t
left join original_hours_th ohth
on t.id = ohth.workitem_id
where ohth.workitem_id is null
),
original_hours_comm_result as (
select
ohth.id,
ohth.workitem_id,
ohth.start,
ohth.time_estimate
from original_hours_th ohth
where ohth.start = max_oh_dt
union all
select
toh.sprints,
toh.id,
toh.st_dt,
toh.time_estimate
from ticket_orig_hours toh
)Remaining hours
Our purpose here is the same as we had on the previous step but here we retrieve remaining hours related data.
remaining_hours_th as (
select
s.id,
th.workitem_id,
th.start,
th.time_remaining,
max(th.start) over (partition by th.workitem_id, s.id) as max_rh_dt
from sprint s
left join tickethistory th
on th.start <= coalesce(s.start_date, s.activated_date)
where th.field = 6
),
ticket_rem_hours as (
select
unnest(t.sprints) as sprints,
t.id,
coalesce(t.done_date, t.created) as st_dt,
t.time_remaining
from ticket t
left join original_hours_th rhth
on t.id = rhth.workitem_id
where rhth.workitem_id is null
),
remaining_hours_comm_result as (
select
rhth.id,
rhth.workitem_id,
rhth.start,
rhth.time_remaining
from remaining_hours_th rhth
where rhth.start = max_rh_dt
union all
select
trh.sprints,
trh.id,
trh.st_dt,
trh.time_remaining
from ticket_rem_hours trh
)The result of the “committed” part of code
Here we join sprints/releases, committed tickets, committed story points, original hours and remaining hours. Then we count tickets and summarize story points, original hours and remaining hours and grouping them by sprints/releases.
committed_result as (
select
s.id as sprint_id,
s.name as sprint_name,
count(sad.wrktm_id) as items,
sum(t.story_points) as story_points,
round(sum(ohcr.time_estimate)/60, 2) as original_hours,
round(sum(rhcr.time_remaining)/60) as remaining_hours
from sprint s
left join
sprint_active_dates sad
on s.id::varchar(255) = trim(sad.added_sprint)
and coalesce(s.start_date, s.activated_date) between sad.added_on and coalesce(sad.gone_on, current_date)
left join
story_points_comm_result t
on sad.wrktm_id = t.workitem_id
and s.id = t.id
left join
original_hours_comm_result ohcr
on sad.wrktm_id = ohcr.workitem_id
and s.id = ohcr.id
left join
remaining_hours_comm_result rhcr
on sad.wrktm_id = rhcr.workitem_id
and s.id = rhcr.id
group by s.id, s.name
)Example of the output:
Completed part
Completed tickets
Here we retrieve data from “ticket“ table related to the tickets with “done“ status. Then we join these tickets to sprints/releases, count tickets, summarize story points, original hours and remaining hours.
all_completed_tickets as (
select distinct
t.done_date as mdt,
t.sprints,
t.key,
t.story_points as sp,
t.time_estimate as orig_hours,
t.time_remaining as rem_hours
from ticket t
where ((select y_n from include_sub_items) <> 1 or (lower(t.type) not like 'sub%' and t.parent_task is null ))
),
completed_result as (
select
s.id as sprint_id,
s.name as sprint_name,
s.finish_date as finish_date,
count(t2.key) as items,
sum(t2.sp) as story_points,
round(sum(t2.orig_hours)/60, 2) as original_hours,
round(sum(t2.rem_hours)/60) as remaining_hours
from sprint s
left join all_completed_tickets t2
on s.id = any(t2.sprints)
and
t2.mdt <= coalesce(s.complete_date, s.finish_date)
group by s.id , s.name, s.finish_date
)Result query
In the final query we gather join “committed_result” query and “completed_result” query to calculate the commitment rate as completed items, story points etc. divide by committed items, story points etc.
select
"Sprint name",
"Items",
"Story points",
"Original hours",
"Remaining hours"
from (
select
s.state,
s.finish_date,
s.start_date,
s.name as "Sprint name",
coalesce(round(comp.items::float/nullif(comm.items, 0)*100), 0) as "Items",
coalesce(round(comp.story_points::float/nullif(comm.story_points, 0)*100), 0) as "Story points",
coalesce(round(comp.original_hours::float/nullif(comm.original_hours, 0)*100), 0) as "Original hours",
coalesce(round(comp.remaining_hours::float/nullif(comm.remaining_hours, 0)*100), 0) as "Remaining hours"
from
sprint s
left join
committed_result comm
on s.id = comm.sprint_id
left join
completed_result comp
on s.id = comp.sprint_id
order by
s.state,
s.finish_date desc,
s.start_date desc,
s.name desc
limit (select number_of_rows from limit_rows)
) a
order by
a.state desc,
a.finish_date,
a.start_date,
a."Sprint name"Example of the output:
Full code recap
Copyright © 2022 EPAM Systems, Inc. |
|---|
All Rights Reserved. All information contained herein is, and remains the property of EPAM Systems, Inc. and/or its suppliers and is protected by international intellectual property law. Dissemination of this information or reproduction of this material is strictly forbidden, unless prior written permission is obtained from EPAM Systems, Inc. |