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

bulk_employees_stats.sql

SELECT
	trem_empl_fk,
	array_agg(ttce_cert_fk) AS ttce_cert_fk,
	array_agg(expiry) AS expiry,
	array_agg(valid) AS valid,
	array_agg(validForAWhile) AS validForAWhile

FROM (
	SELECT
		trem_empl_fk,
		ttce_cert_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_outcome = 'VALIDATED'
		AND trng_date <= current_date
		AND trem_empl_fk in (SELECT siem_empl_fk FROM sites_employees WHERE siem_site_fk = '29873' AND siem_updt_fk = 167)

	GROUP BY (trem_empl_fk, ttce_cert_fk)
) AS employeesStats

GROUP BY trem_empl_fk