NEW - Deployment Frequency over months

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) 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_date_info AS(
	SELECT 
		date_actual, 
		first_day_of_week, 
		first_day_of_month 
	FROM d_date 
	WHERE $__timeFilter(date_actual)
),
deploy_interval_info 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_date_info dd
	LEFT JOIN issues_info dep ON dep.deploy_date = dd.date_actual
	GROUP BY first_day_of_week
)
SELECT
 month + INTERVAL '3 hour' AS time,
	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 deploy_interval_info
GROUP BY month