gistfile1.txt

WITH base_risk AS (
  SELECT
    *,
    (
      (100 - prosperity_score) * 0.10 +
      (100 - safety_and_security) * 0.10 +
      (100 - violent_crime) * 0.30 +
      (100 - property_crime) * 0.25
    ) AS base_risk_score
  FROM your_table
),
modifiers AS (
  SELECT
    *,
    
    -- Flag: Crime Data Exists?
    CASE
      WHEN regional_crime_class IS NOT NULL OR national_crime_class IS NOT NULL THEN 1
      ELSE 0
    END AS crime_data_exists,
    
    -- Crime Multiplier
    CASE
      WHEN regional_crime_class IS NOT NULL THEN
        CASE regional_crime_class
          WHEN 1 THEN 0.90
          WHEN 2 THEN 0.92
          WHEN 3 THEN 0.94
          WHEN 4 THEN 0.96
          WHEN 5 THEN 0.98
          WHEN 6 THEN 1.00
          WHEN 7 THEN 1.02
          WHEN 8 THEN 1.04
          WHEN 9 THEN 1.06
          WHEN 10 THEN 1.08
          WHEN 11 THEN 1.10
          WHEN 12 THEN 1.12
          ELSE 1.00
        END
      WHEN national_crime_class IS NOT NULL THEN
        CASE national_crime_class
          WHEN 1 THEN 0.90
          WHEN 2 THEN 0.92
          WHEN 3 THEN 0.94
          WHEN 4 THEN 0.96
          WHEN 5 THEN 0.98
          WHEN 6 THEN 1.00
          WHEN 7 THEN 1.02
          WHEN 8 THEN 1.04
          WHEN 9 THEN 1.06
          WHEN 10 THEN 1.08
          WHEN 11 THEN 1.10
          WHEN 12 THEN 1.12
          ELSE 1.00
        END
      ELSE 1.00
    END AS crime_multiplier,
    
    -- Structure Density Multiplier
    CASE
      WHEN structure_density_class IS NULL THEN 1.00
      WHEN crime_data_exists = 1 THEN -- Crime data exists: moderate structure influence
        CASE
          WHEN structure_density_class BETWEEN 1 AND 3 THEN 0.95
          WHEN structure_density_class BETWEEN 4 AND 6 THEN 1.00
          WHEN structure_density_class BETWEEN 7 AND 9 THEN 1.05
          WHEN structure_density_class BETWEEN 10 AND 12 THEN 1.10
          ELSE 1.00
        END
      ELSE -- No crime data: stronger structure influence
        CASE
          WHEN structure_density_class BETWEEN 1 AND 3 THEN 0.95
          WHEN structure_density_class BETWEEN 4 AND 6 THEN 1.00
          WHEN structure_density_class BETWEEN 7 AND 9 THEN 1.10
          WHEN structure_density_class BETWEEN 10 AND 12 THEN 1.20
          ELSE 1.00
        END
    END AS structure_multiplier,
    
    -- Population Density Multiplier
    CASE
      WHEN pop_density_class IS NULL THEN 1.00
      WHEN crime_data_exists = 1 THEN -- Crime data exists: moderate pop influence
        CASE
          WHEN pop_density_class BETWEEN 1 AND 3 THEN 0.95
          WHEN pop_density_class BETWEEN 4 AND 6 THEN 1.00
          WHEN pop_density_class BETWEEN 7 AND 9 THEN 1.05
          WHEN pop_density_class BETWEEN 10 AND 12 THEN 1.10
          ELSE 1.00
        END
      ELSE -- No crime data: stronger pop influence
        CASE
          WHEN pop_density_class BETWEEN 1 AND 3 THEN 0.95
          WHEN pop_density_class BETWEEN 4 AND 6 THEN 1.00
          WHEN pop_density_class BETWEEN 7 AND 9 THEN 1.10
          WHEN pop_density_class BETWEEN 10 AND 12 THEN 1.20
          ELSE 1.00
        END
    END AS pop_multiplier,
    
    -- Income Group Multiplier
    CASE adm0_income_grp
      WHEN 'High income: OECD' THEN 1.00
      WHEN 'High income: nonOECD' THEN 1.02
      WHEN 'Upper middle income' THEN 1.05
      WHEN 'Lower middle income' THEN 1.08
      WHEN 'Low income' THEN 1.10
      ELSE 1.00
    END AS income_multiplier

  FROM base_risk
)

SELECT
  country,
  year,
  base_risk_score,
  crime_multiplier,
  structure_multiplier,
  pop_multiplier,
  income_multiplier,
  -- Final Score applying ALL multipliers
  (base_risk_score * crime_multiplier * structure_multiplier * pop_multiplier * income_multiplier) AS total_risk_score
FROM modifiers
;