OLD - Lead Time over 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,
 -- year_actual,
 -- month_actual,
 -- count(title) as throughput,
 MIN(first_day_of_month) + INTERVAL '3 hour' AS time
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)lt
JOIN 
	(SELECT 
		year_actual,
		month_actual, 
		MIN(first_day_of_month) AS first_day_of_month
	 FROM d_date
	 WHERE $__timeFilter(date_actual) 
	 GROUP BY year_actual, month_actual
	 ORDER BY year_actual, month_actual)dates
ON (EXTRACT(year FROM lt.done_datetime) = dates.year_actual and EXTRACT(month FROM lt.done_datetime) = dates.month_actual)
GROUP BY year_actual, month_actual
ORDER BY time