PostgreSQL query template to compute statistics in bulk for sites in NCLS Development's Orca solution

bulk_sites_stats.sql

SELECT
	site,
	array_agg(cert_pk) AS cert_pk,
	array_agg(valid) AS valid,
	array_agg(validForAWhile) AS validForAWhile,
	array_agg(expired) AS expired,
	array_agg(target) AS target,
	array_agg(status) AS status

FROM (
	SELECT
		site,
		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
			siem_site_fk,
			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
				siem_site_fk,
				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
				JOIN sites_employees ON siem_empl_fk = trem_empl_fk

			WHERE
				trem_empl_fk IN (
					SELECT siem_empl_fk FROM sites_employees WHERE siem_site_fk != '0' AND siem_updt_fk = 167
				)
				AND trem_outcome = 'VALIDATED'
				AND trng_date <= current_date

			GROUP BY
				siem_site_fk,
				ttce_cert_fk,
				trem_empl_fk
		) AS itsEmployees

		GROUP BY
			siem_site_fk,
			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_updt_fk = 167
				AND siem_site_fk != '0'

			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
) AS sitesStats

GROUP BY
	site