Merge Request States (Code Review Pass Rate) by Month

1 Context
2 Description
3 Configuration
4 Code
5 Drill-down
6 Variation
7 How to interpret this metric & next steps

Context

Standard Version Control Summary metric shows overall information about merge request states. But what if you want to track this information on a regular basis to be able to follow the trends? In such cases, you may want to create a custom metric specifically for your needs. Here is an example of how to do it.

Description

This metric is based on GIT data.
Metric collects all pull requests submitted in a specific month & breaks them down into 3 buckets (Accepted, In Review & Rejected)

Configuration

We have 1 variable in the code of this metric:

Number of months to show. How many months to show.

Code

Open Bugs Over Time by Priority

with number_of_months_to_show as
(
  select 8 --how many months to show on the chart
  as show
),
 
month as --generating months
(
select       
  (date_trunc('month', now()) - interval '1' month* generate_series(0,(select show from number_of_months_to_show)-1)) as month
),
 
states as --collecting info
(
  select to_char(month, 'YYYY-MM') as month,
  state, title, created_at, merge_date, subject, target_branch
from month a
left join pullrequest b on b.created_at between month and month+interval '1 month'
)
 
select
    month::varchar,
    sum(case when state='COMPLETED' then 1 else 0 end) as "Accepted",
    sum(case when state='ACTIVE' then 1 else 0 end) as "In Review",
    sum(case when state='ABANDONED' then 1 else 0 end) as "Rejected"
from states
group by month
order by month asc

Drill-down

Drill-down query to show Title, Merge Request Creation Date, Repository Name & Target Branch for a clicked month:

select
title, created_at, merge_date, subject, target_branch
from states
where state='COMPLETED'
and month=clicked_x_value

Variation

If there are several repositories you want to track this metric for, you can combine them in one metric:

with month as
(
select       
  (date_trunc('month', now()) - interval '1' month* generate_series(0,7)) as month
),
 
states as
(
  select to_char(month, 'YYYY-MM') as month,
  state, title, created_at, merge_date, subject, target_branch
from month a
left join pullrequest b on b.created_at between month and month+interval '1 month'
)
 
select
    month::varchar,
    100.0*sum(case when state='COMPLETED' and subject='Repo1' then 1 else 0 end)/NULLIF(sum(case when subject='Repo1' then 1 else 0 end),0) as "Repo1",
    100.0*sum(case when state='COMPLETED' and subject='Repo2' then 1 else 0 end)/NULLIF(sum(case when subject='Repo2' then 1 else 0 end),0) as "Repo2",
    100.0*sum(case when state='COMPLETED' and subject='Repo3' then 1 else 0 end)/NULLIF(sum(case when subject='Repo3' then 1 else 0 end),0) as "Repo3"
from states
group by month
order by month asc

In the drill-down you can have information for all not completed merge requests:

How to interpret this metric & next steps

A big number of rejected merge requests can indicate issues with risky or too complicated changes, poor code quality, etc. The trend will help you to understand whether this is a one-time or usual case.
If there are a lot of repositories, branches, it's a good idea to add additional filtering or/and create some more of such metrics for specific repositories.
It would be a good idea to convert number of merge requests to %.
As a next step, it would be good to map rejected merge requests with people to see if there are correlations - e.g. majority of rejected merge requests were submitted by specific developer.

TelescopeAI User Guide