2023-10-27: CA Owner "?" on https://crt.sh/cert-populations

query.sql

WITH cas AS (
	SELECT cac.CA_ID
		FROM ccadb_certificate cc
				JOIN ca_certificate cac ON cc.CERTIFICATE_ID = cac.CERTIFICATE_ID
		WHERE cc.INCLUDED_CERTIFICATE_OWNER IS NULL
	EXCEPT
	SELECT cac.CA_ID
		FROM ccadb_certificate cc
				JOIN ca_certificate cac ON cc.CERTIFICATE_ID = cac.CERTIFICATE_ID
		GROUP BY cac.CA_ID
		HAVING max(coalesce(coalesce(nullif(trim(cc.SUBORDINATE_CA_OWNER), ''), nullif(trim(cc.CA_OWNER), '')), cc.INCLUDED_CERTIFICATE_OWNER)) IS NOT NULL
)
SELECT ca.ID,
		sum(coalesce(ca.NUM_ISSUED[1], 0)) CERT_POPULATION,
		(sum(coalesce(ca.NUM_ISSUED[1], 0)) - sum(coalesce(ca.NUM_EXPIRED[1], 0))) CERT_POPULATION_UNEXPIRED,
		sum(coalesce(ca.NUM_ISSUED[2], 0)) PRECERT_POPULATION,
		(sum(coalesce(ca.NUM_ISSUED[2], 0)) - sum(coalesce(ca.NUM_EXPIRED[2], 0))) PRECERT_POPULATION_UNEXPIRED,
		ca.NAME,
		array_to_string(array_agg(DISTINCT trust.CTX ORDER BY trust.CTX), ',')
	FROM cas
			JOIN ca ON cas.CA_ID = ca.ID
			LEFT JOIN LATERAL (
				SELECT tc.CTX
					FROM ca_trust_purpose ctp, trust_context tc
					WHERE ctp.CA_ID = ca.ID
						AND ctp.TRUST_CONTEXT_ID = tc.ID
			) trust ON TRUE
	GROUP BY ca.ID, ca.NAME
	ORDER BY CERT_POPULATION DESC;

results.csv