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

What is “Bug growth by days” 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 bugs data

bugs as (
    select 
            key as item,
            date_trunc('day', created) as created,
            date_trunc('day', done_date) as d_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’. Also every “done_date“ and “created“ date are truncated to define the beginning of these dates for proper joining

Example of the output:

Data aggregation and grouping

To get required result we need to join logged bugs with fixed bugs. To do so we need to join bugs by equality condition of dates we defined earlier. Then we count unique values of the item-column shown in the output above and group counted values by these dates

Full code recap

with   
    bugs as (
    select 
            key as item,
            date_trunc('day', created) as created,
            date_trunc('day', done_date) as d_date
     from   ticket
    where   
            lower(type) in ('bug', 'sub-bug')
    )
      
    select 
    	    coalesce(logged.created,   
                      fixed.d_date
                    )::timestamp               as days,                    
            count(distinct logged.item)   as logged_items,
            count(distinct fixed.item)    as fixed_items
     from   bugs logged
     full   join
            bugs fixed
       on   logged.created = fixed.d_date
    where   logged.created is not null 
       or   fixed.d_date   is not null
    group   by days
    order   by days desc

Example of the output: