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

site_stats.sql

SELECT
	cert_pk,
	COALESCE(valid, 0) AS valid,
	COALESCE(validForAWhile, 0) AS validForAWhile,
	COALESCE(expired, 0) AS expired,
	ceil(employeesCount * cert_target / 100::numeric) AS target,
	CASE
		WHEN COALESCE (valid, 0) >= ceil(employeesCount * cert_target / 100::numeric)
			THEN 'success'
		WHEN valid >= ceil(employeesCount * 2/3::numeric * cert_target / 100::numeric)
			THEN 'warning'
		ELSE 'danger'
	END AS status

FROM (
	SELECT
		ttce_cert_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

		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

		WHERE
			trem_empl_fk in (
				SELECT siem_empl_fk FROM sites_employees WHERE siem_site_fk = '2274' AND siem_updt_fk = 167
			)
			AND trem_outcome = 'VALIDATED'
			AND trng_date <= current_date

		GROUP BY
			ttce_cert_fk,
			trem_empl_fk
	) AS itsEmployees

	GROUP BY
		ttce_cert_fk
) AS employeesStats

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 = '2274'
			AND siem_updt_fk = 167

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