[GitHub] [incubator-devlake] martincrb commented on pull request #5105: chore(dashboards): add DORA by team dashboard

["martincrb (via GitHub)" <gi...@apache.org>]

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: commits-unsubscribe@devlake.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org