NEW - Deployment Frequency

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

WITH issues_info AS (
	SELECT
		version_id,
		MIN(team_key) as team,
		MIN(status_category_change_date) AS deploy_date
	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) BETWEEN $__timeFrom() AND $__timeTo()
	ORDER BY deploy_date
),
d_data_info AS (
	SELECT 
		date_actual, 
		first_day_of_week, 
		first_day_of_month 
	FROM d_date 
	WHERE $__timeFilter(date_actual)
),
interval_deploy_count AS (
	SELECT
		first_day_of_week AS week,
		MIN(first_day_of_month) AS month,
		MAX(CASE WHEN dep.deploy_date IS NOT NULL THEN 1 ELSE 0 END) AS week_deployed,
		COUNT(DISTINCT dep.deploy_date) AS days_deployed,
		SUM(MAX(CASE WHEN dep.deploy_date IS NOT NULL THEN 1 ELSE 0 END)) OVER(PARTITION BY MIN(first_day_of_month)) AS monthly_deploys
	FROM d_data_info dd
	LEFT JOIN issues_info dep ON dep.deploy_date = dd.date_actual
	GROUP BY first_day_of_week
)
SELECT
	CASE 
		WHEN PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_deployed) >= 3 THEN 'Daily' 
		WHEN PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY week_deployed) >= 1 THEN 'Weekly' 
		WHEN PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY monthly_deploys) >= 1 THEN 'Monthly' 
		ELSE 'Yearly'
	END AS deployment_frequency
FROM interval_deploy_count