[Feature][Dashboard] Use month of merged_date to group for "mean time to merge pull requests"

[asterix314]

Search before asking

• [X] I had searched in the issues and found no similar feature requirement.

Description

In the "GitHub Basic Metrics" dashboard, the calculation of "mean time to merge pull requests" is anchored on the PR creation date, as per Inspect/Query, or in simplified SQL:

SELECT TRUNCATE_MONTH(created_date) AS created_month, AVG(merged_date - created_date) AS time_to_merge
FROM pull_requests
GROUP BY created_month

The resulting bar chart would most likely have a downward trend towards the right end, such as the following:

It is because the time to merge must by necessity be smaller than the PR's age, so young PR's appear to be merged sooner than old ones on average. A bias on the PR's age is thus introduced.

This feature request asks that the same calculation be anchored on the PR merge date instead:

SELECT TRUNCATE_MONTH(merged_date) AS merged_month, AVG(merged_date - created_date) AS time_to_merge
FROM pull_requests
GROUP BY merged_month

which should get rid of the bias.

Note the same bias also manifests in "mean time to close pull requests", and potentially in other dashboards, too, where an average of some interval is calculated anchoring on the beginning time.

Use case

No response

Related issues

No response

Are you willing to submit a PR?

• [x] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[Startrekzky]

Good catch @asterix314 . I noticed this problem and got it fixed in v0.12.0. May I know the version number you're using?

[asterix314]

Good catch @asterix314 . I noticed this problem and got it fixed in v0.12.0. May I know the version number you're using?

Great then. I noticed the issue in v0.11.

[asterix314]

Hi @Startrekzky

I just had a quick check at the default and release-v0.12 branches. In both cases the relevant code snippet (in grafana/dashboards/Gitlab.json ) was:

with _prs as(
  SELECT
    DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as time,
    avg(TIMESTAMPDIFF(Minute,created_date,merged_date)/1440) as time_to_merge
  FROM pull_requests
  WHERE
    $__timeFilter(created_date)
    and base_repo_id in ($repo_id)
    and created_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
  group by 1
)

which is till grouping by the month of create_date. Did I look at the wrong file?

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity for 30 days. It will be closed in next 7 days if no further activity occurs.

[github-actions[bot]]

This issue has been closed because it has not received response for too long time. You could reopen it if you encountered similar problems in the future.