There were a couple missing ) and a couple missing ". I might recommend using something like https://www.pgadmin.org/ to help with finding that kind of stuff with syntax highlighting (especially if you’re using the data warehouse). Additionally, one thing that needed to be updated was the main SELECT:
SELECT da.asset_id, 0 AS “<30m”, 0 AS “<30s”, 0 AS “<30c”, 0 AS “31-60m”, 0 AS “31-60s”, 0 AS “31-60c”, 0 AS “61-90m”, 0 AS “61-90s”, 0 AS “61-90c”, 0 AS “91+m”, 0 AS “91+s”, 0 AS “91+c”
Note that it doesn’t match the column names in the subquery. The names need to match (as you’ll see below), but that was a separate issue to the missing quotes/parens. Here’s an updated one that should work.
WITH vuln_result_categorization AS (
SELECT asset_id,
CASE WHEN (age_in_days <= 30 AND greatest_cvss_score < 4) THEN 1 ELSE 0 END AS "<30L",
CASE
WHEN (age_in_days <= 30 AND greatest_cvss_score >= 4 AND greatest_cvss_score < 7) THEN 1
ELSE 0 END AS "<30M",
CASE
WHEN (age_in_days <= 30 AND greatest_cvss_score >= 7) AND greatest_cvss_score < 9 THEN 1
ELSE 0 END AS "<30H",
CASE
WHEN (age_in_days <= 30 AND greatest_cvss_score > 8.9) THEN 1
ELSE 0 END AS "<30C",
CASE
WHEN (age_in_days <= 60 AND age_in_days >= 31 AND greatest_cvss_score < 4) THEN 1
ELSE 0 END AS "31-60L",
CASE
WHEN (age_in_days <= 60 AND age_in_days >= 31 AND greatest_cvss_score >= 4 AND greatest_cvss_score < 7)
THEN 1
ELSE 0 END AS "31-60M",
CASE
WHEN (age_in_days <= 60 AND age_in_days >= 31 AND greatest_cvss_score >= 7) AND greatest_cvss_score < 9
THEN 1
ELSE 0 END AS "31-60H",
CASE
WHEN (age_in_days <= 60 AND age_in_days >= 31 AND greatest_cvss_score >= 8.9) THEN 1
ELSE 0 END AS "31-60C",
CASE
WHEN (age_in_days <= 90 AND age_in_days >= 61 AND greatest_cvss_score < 4) THEN 1
ELSE 0 END AS "61-90L",
CASE
WHEN (age_in_days <= 90 AND age_in_days >= 61 AND greatest_cvss_score >= 4 AND greatest_cvss_score < 7)
THEN 1
ELSE 0 END AS "61-90M",
CASE
WHEN (age_in_days <= 90 AND age_in_days >= 61 AND greatest_cvss_score >= 7) AND greatest_cvss_score < 9
THEN 1
ELSE 0 END AS "61-90H",
CASE
WHEN (age_in_days <= 90 AND age_in_days >= 61 AND greatest_cvss_score >= 8.9) THEN 1
ELSE 0 END AS "61-90C",
CASE WHEN (age_in_days > 90 AND greatest_cvss_score < 4) THEN 1 ELSE 0 END AS "91+L",
CASE
WHEN (age_in_days > 90 AND greatest_cvss_score >= 4 AND greatest_cvss_score < 7) THEN 1
ELSE 0 END AS "91+M",
CASE
WHEN (age_in_days > 90 AND greatest_cvss_score >= 7 AND greatest_cvss_score >= 8.9) THEN 1
ELSE 0 END AS "91+H",
CASE WHEN (age_in_days > 90 AND greatest_cvss_score >= 9) THEN 1 ELSE 0 END AS "91+C"
FROM (
SELECT fav.asset_id,
fav.vulnerability_id,
fava.age_in_days,
GREATEST(dv.cvss_v3_score, dv.cvss_score) AS greatest_cvss_score
FROM fact_asset_vulnerability_finding fav
JOIN dim_vulnerability dv ON fav.vulnerability_id = dv.vulnerability_id
JOIN fact_asset_vulnerability_age fava
ON fava.asset_id = fav.asset_id AND fava.vulnerability_id = fav.vulnerability_id
GROUP BY fav.asset_id, fav.vulnerability_id, dv.date_published, GREATEST(dv.cvss_v3_score, dv.cvss_score),
fava.age_in_days
) avd
)
SELECT dag.name AS asset_group,
fag.assets AS asset_count,
fag.vulnerabilities AS vulnerabilities,
SUM("<30C") AS "<30_Critical",
SUM("31-60C") AS "31-60_Critical",
SUM("61-90C") AS "61-90_Critical",
SUM("91+C") AS "91+_Critical",
SUM("<30H") AS "<30_High",
SUM("31-60H") AS "31-60_High",
SUM("61-90H") AS "61-90_High",
SUM("91+H") AS "91+_High",
SUM("<30M") AS "<30_Med",
SUM("31-60M") AS "31-60_Med",
SUM("61-90M") AS "61-90_Med",
SUM("91+M") AS "91+_Med",
SUM("<30L") AS "<30_Low",
SUM("31-60L") AS "31-60_Low",
SUM("61-90L") AS "61-90_Low",
SUM("91+L") AS "91+_Low"
FROM (
SELECT da.asset_id,
0 AS "<30L",
0 AS "<30M",
0 AS "<30H",
0 AS "<30C",
0 AS "31-60L",
0 AS "31-60M",
0 AS "31-60H",
0 AS "31-60C",
0 AS "61-90L",
0 AS "61-90M",
0 AS "61-90H",
0 AS "61-90C",
0 AS "91+L",
0 AS "91+M",
0 AS "91+H",
0 AS "91+C"
FROM dim_asset da
WHERE da.asset_id IN (SELECT asset_id FROM vuln_result_categorization)
UNION ALL
SELECT *
FROM vuln_result_categorization
) v
JOIN dim_asset_group_asset daga
ON daga.asset_id = v.asset_id
JOIN dim_asset_group dag ON daga.asset_group_id = dag.asset_group_id
JOIN fact_asset_group fag ON daga.asset_group_id = fag.asset_group_id
WHERE dag.asset_group_id IN (SELECT asset_group_id FROM dim_scope_asset_group)
GROUP BY dag.name, fag.assets, fag.vulnerabilities;
