martincrb commented on PR #5105:
URL:
https://github.com/apache/incubator-devlake/pull/5105#issuecomment-1538470984
Hi @Startrekzky , sure. This are the queries i used:
**Deployment Frequency**
```sql
-- Metric 1: Deployment Frequency
-- [SAME AS PER PROJECT DORA CODE]
SELECT
cdc.cicd_deployment_id as deployment_id,
max(DATE(cdc.finished_date)) as day
FROM cicd_deployment_commits cdc
JOIN commits c on cdc.commit_sha = c.sha
JOIN user_accounts ua on c.author_id = ua.account_id
JOIN users u on ua.user_id = u.id
JOIN team_users tu on u.id = tu.user_id
JOIN teams t on tu.team_id = t.id
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
WHERE
t.name in ($team)
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
),
-- [SAME AS PER PROJECT DORA CODE]
```
I get first thhe author of the specific commit and then i navigate to the
team the user belongs to.
**Median Lead Time for Changes**
```sql
-- Metric 2: median lead time for changes
with _pr_stats as (
-- get the cycle time of PRs deployed by the deployments finished in the
selected period
SELECT
distinct pr.id,
ppm.pr_cycle_time
FROM
pull_requests pr
JOIN user_accounts ua on pr.author_id = ua.account_id
JOIN users u on ua.user_id = u.id
JOIN team_users tu on u.id = tu.user_id
JOIN teams t on tu.team_id = t.id
JOIN project_pr_metrics ppm on ppm.id = pr.id
JOIN project_mapping pm on pr.base_repo_id = pm.row_id
JOIN cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id
WHERE
t.name in ($team)
and pr.merged_date is not null
and ppm.pr_cycle_time is not null
and $__timeFilter(cdc.finished_date)
),
-- [SAME AS PER PROJECT DORA CODE]
```
I get the team from the PR's author.
**Median Time to Restore Service**
```sql
-- Metric 3: Median time to restore service
with _incidents as (
-- get the incidents created within the selected time period in the
top-right corner
SELECT
distinct i.id,
cast(lead_time_minutes as signed) as lead_time_minutes
FROM
issues i
join board_issues bi on i.id = bi.issue_id
join boards b on bi.board_id = b.id
join project_mapping pm on b.id = pm.row_id
join user_accounts ua on i.assignee_id = ua.account_id
join users u on ua.user_id = u.id
join team_users tu on u.id = tu.user_id
join teams t on tu.team_id = t.id
WHERE
t.name in ($team)
and i.type = 'INCIDENT'
and $__timeFilter(i.created_date)
),
-- [SAME AS PER PROJECT DORA CODE]
```
I get the team from the user account assignee.
**Change Failure Rate**
```sql
-- Metric 4: change failure rate
with _deployments as (
-- When deploying multiple commits in one pipeline, GitLab and BitBucket may
generate more than one deployment. However, DevLake consider these deployments
as ONE production deployment and use the last one's finished_date as the
finished date.
SELECT
cdc.cicd_deployment_id as deployment_id,
max(cdc.finished_date) as deployment_finished_date
FROM
cicd_deployment_commits cdc
JOIN commits c on cdc.commit_sha = c.sha
JOIN user_accounts ua on c.author_id = ua.account_id
JOIN users u on ua.user_id = u.id
JOIN team_users tu on u.id = tu.user_id
JOIN teams t on tu.team_id = t.id
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
WHERE
t.name in ($team)
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
HAVING $__timeFilter(max(cdc.finished_date))
),
-- [SAME AS PER PROJECT DORA CODE]
```
I get the team from the commit author.
Let me know if there is anything that seems wrong! Copied some joins from
other dashboards that are Team based.
Thank you!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
