NEW - Lead Time over time

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

WITH lead_time_info AS (
	SELECT
		PR.title,
		JI.status_category_change_date_time AS done_datetime,
		EXTRACT(EPOCH FROM JI.status_category_change_date_time - PR.created_date_time) AS lead_time_created,
		EXTRACT(EPOCH FROM JI.status_category_change_date_time - PR.merged_date_time) AS lead_time_merged
	FROM jira.jira_issues_summary JI
	INNER JOIN github.git_pull_requests_summary PR ON JI.key = PR.key
	WHERE
		JI.status_name = 'DONE' AND
		JI.team_key IN ($teams) AND
		PR.state = 'closed' AND
		$__timeFilter(JI.status_category_change_date_time)
	ORDER BY lead_time_created DESC
),
d_dates_info AS (
	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
)
SELECT
	PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lt.lead_time_created) AS median_lead_time_created,
	PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lt.lead_time_merged) AS median_lead_time_merged,
	PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY lt.lead_time_merged) AS seventyfive_lead_time_merged,
	AVG(lt.lead_time_merged) AS avg_lead_time_merged,
	count(lt.title) as Throughput,
	MIN(dates.first_day_of_month) + INTERVAL '3 hour' AS time
FROM lead_time_info lt
JOIN d_dates_info dates ON (EXTRACT(year FROM lt.done_datetime) = dates.year_actual and EXTRACT(month FROM lt.done_datetime) = dates.month_actual)
GROUP BY dates.year_actual, dates.month_actual
ORDER BY time