OLD - Deployment Frequency over months

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

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
	(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
		(SELECT 
			date_actual, 
			first_day_of_week, 
			first_day_of_month 
		FROM d_date 
		WHERE $__timeFilter(date_actual))dd
		LEFT JOIN 
		(SELECT 
			version_id,
			MIN(team) AS team,
			MIN(deploy_date) AS deploy_date
		FROM
			(SELECT
				fields -> 'fixVersions' -> 0 ->> 'id' AS version_id,
				fields -> 'project' ->> 'key' AS team,
				TO_TIMESTAMP(fields ->> 'statuscategorychangedate', 'YYYY-MM-DD') AS deploy_date
			FROM 
				jira.jira_issues
			WHERE
				fields -> 'status' ->> 'name' = 'DONE')raw
		WHERE version_id IS NOT NULL
		AND team IN ($teams)
		AND $__timeFilter(deploy_date)
		GROUP BY version_id
		ORDER BY deploy_date)dep
	ON dep.deploy_date = dd.date_actual
	GROUP BY first_day_of_week)days
GROUP BY month