1SELECT
2  DATE_TRUNC('week', published)::DATE AS week_start,
3  COUNT(
4    CASE
5      WHEN cvss_v31_base_severity = 'CRITICAL' THEN 1
6    END
7  ) AS critical,
8  COUNT(
9    CASE
10      WHEN cvss_v31_base_severity = 'HIGH' THEN 1
11    END
12  ) AS high,
13  COUNT(
14    CASE
15      WHEN cvss_v31_base_severity = 'MEDIUM' THEN 1
16    END
17  ) AS medium,
18  COUNT(
19    CASE
20      WHEN cvss_v31_base_severity = 'LOW' THEN 1
21    END
22  ) AS low,
23  COUNT(
24    CASE
25      WHEN cvss_v31_base_severity NOT IN ('CRITICAL', 'HIGH', 'MEDIUM', 'LOW')
26      OR cvss_v31_base_severity IS NULL THEN 1
27    END
28  ) AS unscored,
29  COUNT(*) AS total_cves
30FROM
31  "@nist.nvd.cves"
32WHERE
33  published >= '2024-01-01'
34  AND published < '2026-05-18'
35GROUP BY
36  DATE_TRUNC('week', published)::DATE
37ORDER BY
38  week_start ASC