PostgreSQL query template to compute statistics for a single department in NCLS Development's Orca solution

department_stats.sql

SELECT
	cert_pk,
	COALESCE(sum(valid), 0) as valid,
	COALESCE(sum(validForAWhile), 0) as validForAWhile,
	COALESCE(sum(expired), 0) as expired,
	count(*) FILTER (WHERE COALESCE(valid, 0) >= ceil(employeesCount * cert_target / 100::numeric)) as success,
	count(*) FILTER (WHERE COALESCE(valid, 0) < ceil(employeesCount * cert_target / 100::numeric) AND valid >= ceil(employeesCount * 2/3::numeric * cert_target / 100::numeric)) as warning,
	count(*) FILTER (WHERE COALESCE(valid, 0) < ceil(employeesCount * 2/3::numeric * cert_target / 100::numeric)) as danger
	
FROM (
	SELECT
		ttce_cert_fk,
		siem_site_fk,
		count(trem_empl_fk) FILTER (WHERE valid = true) as valid,
		count(trem_empl_fk) FILTER (WHERE validForAWhile = true) as validForAWhile,
		count(trem_empl_fk) FILTER (WHERE valid = false) as expired

	FROM (
		SELECT
			ttce_cert_fk,
			trem_empl_fk,
			max(trng_date + trty_validity * INTERVAL '1 month')::date AS expiry,
			max(trng_date + trty_validity * INTERVAL '1 month') > current_date AS valid,
			max(trng_date + trty_validity * INTERVAL '1 month') > current_date + INTERVAL '6 month' as validForAWhile,
			siem_site_fk

		FROM
			trainingtypes_certificates
			JOIN trainingtypes ON trty_pk = ttce_trty_fk
			JOIN trainings ON trng_trty_fk = trty_pk
			JOIN trainings_employees ON trem_trng_fk = trng_pk
			JOIN sites_employees ON siem_empl_fk = trem_empl_fk

		WHERE
			siem_site_fk in (SELECT site_pk FROM sites WHERE site_dept_fk = 17)
			AND siem_updt_fk = 167
			AND trem_outcome = 'VALIDATED'
			AND trng_date <= current_date

		GROUP BY
			ttce_cert_fk,
			trem_empl_fk,
			siem_site_fk
	) AS individualEmployeesStats
	
	GROUP BY
		ttce_cert_fk,
		siem_site_fk
) AS siteEmployeesStats

RIGHT JOIN (
	SELECT
		site,
		employeesCount,
		cert_pk,
		cert_target
	
	FROM (
		SELECT
			siem_site_fk as site,
			count(siem_empl_fk) as employeesCount

		FROM
			sites_employees
			
		WHERE
			siem_site_fk in (SELECT site_pk FROM sites WHERE site_dept_fk = 17)
			AND siem_updt_fk = 167

		GROUP BY siem_site_fk
	) AS sitesEmployeesCounts
	
	FULL OUTER JOIN certificates ON 1=1
) AS sitesCertificates ON site = siem_site_fk AND cert_pk = ttce_cert_fk

GROUP BY
	cert_pk