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

What is “Bug growth by weeks” 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,

            case trim(to_char(done_date, 'day'))
            when 'sunday'
            then date_trunc('day', done_date)
            else date_trunc('week', done_date) - interval '1 day'
            end
            as start_of_the_week_done,
            
            case trim(to_char(done_date, 'day'))
            when 'sunday'
            then date_trunc('day', done_date)
            else date_trunc('week', done_date) - interval '1 day'
            end 
            +    '6 day 23 hour 59 minute 59 second'
            as end_of_the_week_done,

            case trim(to_char(created, 'day'))
            when 'sunday'
            then date_trunc('day', created)
            else date_trunc('week', created) - interval '1 day'
            end
            as start_of_the_week_crtd,
            
            case trim(to_char(created, 'day'))
            when 'sunday'
            then date_trunc('day', created)
            else date_trunc('week', created) - interval '1 day'
            end 
            +    '6 day 23 hour 59 minute 59 second'
            as end_of_the_week_crtd
     from   ticket
    where   
            lower(type) in ('bug', 'sub-bug')
            and (created is not null or done_date is not null)
    )

Here we retrieve all bugs related data filtering data in Ticket table by equality condition of type-column to ‘Bug' or 'Sub-bug’. Also the week borders for every “done_date” and “created“ date are defined

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 use dates of the start of the weeks we defined earlier and join bugs by equality condition of these dates. Then we count unique values of item-column shown in the output above and extract month and day from every start date and end date of the week to concatenate them with the year also extracted from start week date to group counted values by these dates and to get proper output

Full code recap

with   
    bugs as (
    select 
            key as item,

            case trim(to_char(done_date, 'day'))
            when 'sunday'
            then date_trunc('day', done_date)
            else date_trunc('week', done_date) - interval '1 day'
            end
            as start_of_the_week_done,
            
            case trim(to_char(done_date, 'day'))
            when 'sunday'
            then date_trunc('day', done_date)
            else date_trunc('week', done_date) - interval '1 day'
            end 
            +    '6 day 23 hour 59 minute 59 second'
            as end_of_the_week_done,

            case trim(to_char(created, 'day'))
            when 'sunday'
            then date_trunc('day', created)
            else date_trunc('week', created) - interval '1 day'
            end
            as start_of_the_week_crtd,
            
            case trim(to_char(created, 'day'))
            when 'sunday'
            then date_trunc('day', created)
            else date_trunc('week', created) - interval '1 day'
            end 
            +    '6 day 23 hour 59 minute 59 second'
            as end_of_the_week_crtd
     from   ticket
    where   
            lower(type) in ('bug', 'sub-bug')
            and (created is not null or done_date is not null)
    )
      
    select  
            logged.start_of_the_week_crtd as for_sorting,
            coalesce(
    		to_char(
            logged.start_of_the_week_crtd, 'Mon dd')
            ||' - '||
            to_char(
            logged.end_of_the_week_crtd, 'Mon dd')
            ||', '||
            to_char(
            logged.start_of_the_week_crtd, 'yyyy'),

            to_char(
            logged.start_of_the_week_done, 'Mon dd')
            ||' - '||
            to_char(
            logged.end_of_the_week_done, 'Mon dd')
            ||', '||
            to_char(
            logged.start_of_the_week_done, 'yyyy')
            ) as week,
            
            count(distinct logged.item)    as logged_items,
            count(distinct fixed.item)     as fixed_items
     from   bugs logged
     full   join
            bugs fixed
       on   logged.start_of_the_week_crtd = fixed.start_of_the_week_done
    where   logged.start_of_the_week_crtd  is not null 
       or   fixed.start_of_the_week_done   is not null
    group   by week, for_sorting
    order   by for_sorting desc

Example of the output: