OLD - Lead Time

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

SELECT
 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lead_time_created) AS median_lead_time_created,
 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lead_time_merged) AS median_lead_time_merged,
	PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY lead_time_merged) AS seventyfive_lead_time_merged,
	AVG(lead_time_merged) AS avg_lead_time_merged,
 count(title) as Throughput
FROM
 (SELECT
 	title,
 	done_datetime,
 	EXTRACT(EPOCH FROM done_datetime - created_datetime) AS lead_time_created,
 	EXTRACT(EPOCH FROM done_datetime - merged_datetime) AS lead_time_merged
 FROM
 	(SELECT
 		title,
 		key,
 		TO_TIMESTAMP(created_at, 'YYYY-MM-DDXHH24:MI:SS') AS created_datetime,
 		TO_TIMESTAMP(merged_at, 'YYYY-MM-DDXHH24:MI:SS') AS merged_datetime,
 		JI.fields ->> 'summary' as summary,
 		TO_TIMESTAMP(JI.fields ->> 'statuscategorychangedate', 'YYYY-MM-DDXHH24:MI:SS') AS done_datetime
 	FROM 
 		github.git_pull_requests PR, jira.jira_issues JI
 	WHERE
 		JI.fields -> 'status' ->> 'name' = 'DONE'
 	AND
	 JI.fields -> 'project' ->> 'key' IN ($teams)
	 AND
		 PR.title ~* ('((.*))' || JI.key || '(?![0-9])')
 	AND
 		PR.state = 'closed')raw
 WHERE $__timeFilter(done_datetime)
 ORDER BY lead_time_created DESC)media;