This article describes what “Bug growth by sprints” metric is and how it helps and works

What is “Bug growth by sprints” metric?

Bug Growth shows a number of Fixed defects vs. number of Logged defects for a time frame (month, sprint and so on). This view shows a trend of submitted defects exceeds the trend of defects being fixed - to understand if a team can timely manage the quality debt

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

Retrieving of all sprints we need

sprints as (
    select 
            id            as id,
            name          as sprint_name,
            start_date    as start_date,
            finish_date   as f_date,
            complete_date as c_date
      from  sprint
     where  start_date <= now()
)

We retrieve data for all sprints except future ones

Example of the output:

Retrieving of bugs data

bugs as (
    select 
            key as item,
            created as created,
            done_date as d_date,
            resolved as rslv_date
     from   ticket
    where   
            lower(type) in ('bug', 'sub-bug')
)

Here we retrieve all bugs related data filtering data in Ticket table by equality condition of type-column to ‘Bug' or 'Sub-bug’

Example of the output:

Data aggregation and grouping

To get required result we need to join the bugs we have to the sprints twice: firstly to get logged bugs, secondly to get fixed bugs. On the first step the date of bug creation must be between sprint start date and sprint finish date. On the second step bug done date must be between sprint start date and sprint finish date. Then we need to count unique values of logged and fixed bugs and group them by sprint name

Full code recap

with 
sprints as (
    select 
            id            as id,
            name          as sprint_name,
            start_date    as start_date,
            finish_date   as f_date,
            complete_date as c_date
      from  sprint
     where  start_date <= now()
),

bugs as (
    select 
            key as item,
            created as created,
            done_date as d_date,
            resolved as rslv_date
     from   ticket
    where   
            lower(type) in ('bug', 'sub-bug')
)

    select 
            sprints.sprint_name,
            count(distinct logged.item) as logged_bugs,
            count(distinct fixed.item)  as fixed_bugs
    from    sprints
    left    join bugs logged
      on    logged.created between sprints.start_date 
                                and 
                                coalesce(sprints.c_date, sprints.f_date)
    left    join bugs fixed
      on    fixed.d_date between sprints.start_date 
                                and
                                coalesce(sprints.c_date, sprints.f_date)
   group    by sprints.sprint_name
   order    by sprints.sprint_name

Example of the output: