SQL example - most vulnerable site by risk score per asset

This example query shows the most vulnerable site by risk score per asset.

The output shows:

Site
# of assets
Site Risk Score
Risk per Asset

sql
1
SELECT ds.name AS site, assets, riskscore,
2
3
(CASE riskscore WHEN 0 THEN NULL ELSE riskscore END) / (CASE assets WHEN 0 THEN NULL ELSE assets END) AS "RiskPerAsset"
4
5
FROM fact_site
6
7
JOIN dim_site ds USING (site_id)
8
9
ORDER BY "RiskPerAsset" DESC