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

What is “Bug growth by releases” 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 releases we need

releases as (
    select 
            id            as id,
            name          as rel_name,
            start_date    as start_date,
            finish_date   as f_date,
            closed
      from  release
     where  start_date <= now()
     )

We retrieve data for all releases 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 releases twice: firstly to get logged bugs, secondly to get fixed bugs. On the first step the date of bug creation must be between release start date and release finish date. On the second step bug done date must be between release start date and release finish date. Then we need to count unique values of logged and fixed bugs and group them by release name

Full code recap

with 
releases as (
    select 
            id            as id,
            name          as rel_name,
            start_date    as start_date,
            finish_date   as f_date,
            closed
      from  release
     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 
            releases.rel_name,
            count(distinct logged.item) as logged_bugs,
            count(distinct fixed.item)  as fixed_bugs
    from    releases
    left    join bugs logged
      on    logged.created between releases.start_date 
                                and 
                                releases.f_date
    left    join bugs fixed
      on    fixed.d_date between releases.start_date 
                                and
                                releases.f_date
   group    by releases.rel_name
   order    by releases.rel_name

Example of the output: