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, ',').
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.
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.
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.
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.
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.
Remaining hours
Our purpose here is the same as we had on the previous step but here we retrieve remaining hours related data.
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.
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.
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.
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. |