Querying crt.sh for name constrained CAs

ncca.sql

SELECT
  crtshid,
  caid,
  sdn,
  rootowner,
  extensions
FROM
  (WITH trusted_cas AS
     (SELECT DISTINCT ctp.CA_ID
      FROM ca_trust_purpose ctp
      WHERE ctp.TRUST_CONTEXT_ID = 5 -- Mozilla.
        AND ctp.IS_TIME_VALID -- Subject CA has at least 1 Unexpired chain.
        AND ctp.TRUST_PURPOSE_ID IN (
			-- 1 -- ,  -- Server Authentication.
            3 -- Secure Email.
 ) ) SELECT cert.ID AS crtshID,
            tca.CA_ID caID,
            RANK() OVER (PARTITION BY clc.SUBJECT_DISTINGUISHED_NAME
                         ORDER BY clc.not_before DESC) dest_rank,
                        clc.SUBJECT_DISTINGUISHED_NAME sdn,
                        cca.included_certificate_owner rootowner,
                        x509_print(cert.CERTIFICATE, NULL, 7935) extensions
   FROM trusted_cas tca,
        ca_certificate cac,
        certificate_lifecycle clc,
        ccadb_certificate cca,
        certificate cert
   WHERE tca.CA_ID = cac.ca_id
     AND cac.CERTIFICATE_ID = clc.CERTIFICATE_ID
     AND x509_hasExtension(cert.CERTIFICATE, '2.5.29.30') -- Name Constraints

     AND clc.expired = FALSE
     AND clc.CERTIFICATE_ID = cca.certificate_id
     AND clc.CERTIFICATE_ID = cert.id
     AND clc.revoked = 0 -- hide all revoked certificates

     AND cca.included_certificate_owner <> 'HARICA' -- they are special
  ) cas
WHERE cas.dest_rank = 1 -- only use most recent certificate version
ORDER BY rootowner, sdn