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