Table: azure_sql_server_database_vulnerability_assessment_scans

WITH
  safe_dbs
    AS (
      SELECT
        s.id AS sql_database_id
      FROM
        azure_sql_server_database_vulnerability_assessment_scans AS s
        JOIN (
            SELECT
              _cq_id, max((properties->>'endTime')::TIMESTAMP) AS max_dt
            FROM
              azure_sql_server_database_vulnerability_assessment_scans
            GROUP BY
              _cq_id
          )
            AS t ON
            s._cq_id = t._cq_id
            AND (properties->>'endTime')::TIMESTAMP = t.max_dt
      WHERE
        (s.properties->>'numberOfFailedSecurityChecks')::INT8 = 0
    )
SELECT
  'SQL databases should have vulnerability findings resolved' AS title,
  s.subscription_id,
  d.id,
  CASE
  WHEN d.id IS NULL THEN 'fail'
  ELSE 'pass'
  END
FROM
  azure_sql_servers AS s
  LEFT JOIN azure_sql_server_databases AS d ON s._cq_id = d._cq_parent_id
  LEFT JOIN safe_dbs AS sd ON d.id = sd.sql_database_id;