This article describes what “Average velocity by sprints” metric is and how it helps and works

1 What is “Average velocity by sprints” metric?
2 Creating a metric
3 Creating a Custom Selection
4 Including/excluding sub-items
5 Retrieving last 12 closed sprints
6 Joining of the last 12 sprints and related tickets
7 Full code recap

What is “Average velocity by sprints” metric?

Average Velocity shows an amount of value (in story points or items) delivered within 1 - 3 - 6 - 12 sprints. Average Velocity helps to compare the productivity of a team on a long-term versus a short-term time interval - it reveals if an overall performance improves or degrades

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

First of all we need to decide if we want to include sub-items into the calculation or not. To include sub-items we need to change number “1” in the first query to any other number, for example 0 or 2. If there is a need to exclude sub-items - skip this point (the query must look as shown below)

include_sub_items as 
(    
select 1          -- 1 - exclude sub-items, any other number - include sub-items
    as y_n
)

Retrieving last 12 closed sprints

To retrieve last 12 sprints we need to filter sprint table where “state” column value equals 'Closed', sort the result by “complete_date” in reversed order and fetch first 12 rows

sprints as (
       select s.id, 
       		  s.start_date, 
       		  s.complete_date, 
       		  s.finish_date
         from sprint s
        where lower(s.state) = 'closed'
        order by s.complete_date desc
        limit 12
          )

Example of the output:

Joining of the last 12 sprints and related tickets

For proper joining sprints and tickets we need to take all of 12 sprints and join tickets by equality of id in Sprint table and any value of “sprints” column in Ticket table. Also we need to retrieve only those tickets which were completed and got Done-status before a sprint was finished. The result set of this query is sorted descending by “complete_date“ for proper calculation from last sprint to last 12 sprints

spsanditems as (
       select s.id,
              max(coalesce(s.complete_date, s.finish_date)) as complete_date,
              count(wi.key)        							as items,
              sum(wi.story_points) 							as story_points
         from sprints s
         left join ticket wi on s.id = ANY (wi.sprints) 
          and wi.done_date < coalesce(s.complete_date, s.finish_date)
          and ((select * from include_sub_items) <> 1 OR wi.parent_task is null)
       	  and lower(wi.status) in ('done', 'closed', 'resolved')
        group by s.id
        order by complete_date desc -- join tickets and aggregate story points and items per sprint
     )

Example of the output:

Full code recap

Finally we need to combine four similar queries to aggregate data within last 1 - 3 - 6 - 12 weeks

Example of the output: