NEW - Time to Deploy

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

WITH issues_info AS (
	SELECT
		version_id,
		MIN(status_category_change_date_time) AS deploy_datetime,
		MIN(status_category_change_date) AS deploy_date,
		LAG(MIN(status_category_change_date_time), 1) OVER (ORDER BY MIN(status_category_change_date_time)) last_deploy_datetime
	FROM 
		jira.jira_issues_summary
	WHERE
		status_name = 'DONE' AND
		team_key IN ($teams) AND
		version_id IS NOT NULL
	GROUP BY version_id
	HAVING MIN(status_category_change_date_time) BETWEEN $__timeFrom() AND $__timeTo()
	ORDER BY deploy_date
),
d_date_info AS (
	SELECT 
 		first_day_of_month, 
 		MIN(year_actual) AS year_actual, 
 		MIN(month_actual) AS month_actual
 	FROM d_date
 	WHERE $__timeFilter(date_actual)
 	GROUP BY first_day_of_month
 	ORDER BY first_day_of_month
),
month_year_diff_time AS (
	SELECT 
		EXTRACT(EPOCH FROM deploy_datetime - last_deploy_datetime) AS time_diff,
		first_day_of_month
	FROM issues_info dep
	JOIN d_date_info dates ON (EXTRACT(YEAR FROM dep.deploy_date) = dates.year_actual AND EXTRACT(MONTH FROM dep.deploy_date) = dates.month_actual)
)
SELECT
	first_day_of_month + INTERVAL '3 hour' AS time,
	AVG(time_diff) AS avg,
	PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY time_diff) AS median,
	PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY time_diff) AS seventyfive
FROM month_year_diff_time
GROUP BY first_day_of_month
ORDER BY first_day_of_month