gistfile1.txt

WITH base_risk AS (
  SELECT
    *,
    (
      -- Base risk calculation using already inverted scores
      (prosperity_score_inverted * 0.35) +
      (safety_and_security_inverted * 0.25) +
      (violent_crime_inverted * 0.20) +
      (property_crime_inverted * 0.10) +
      (personal_freedom_inverted * 0.10)
    ) AS base_risk_score
  FROM your_cells_table -- <--- REPLACE with your actual table name
),
modifiers AS (
  SELECT
    *,
    
    -- 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
        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
          WHEN structure_density_class BETWEEN 13 AND 16 THEN 1.12
          ELSE 1.00
        END
      ELSE
        CASE
          WHEN structure_density_class BETWEEN 1 AND 3 THEN 0.90
          WHEN structure_density_class BETWEEN 4 AND 6 THEN 0.95
          WHEN structure_density_class BETWEEN 7 AND 9 THEN 1.05
          WHEN structure_density_class BETWEEN 10 AND 12 THEN 1.15
          WHEN structure_density_class BETWEEN 13 AND 16 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
        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
          WHEN pop_density_class BETWEEN 13 AND 16 THEN 1.12
          ELSE 1.00
        END
      ELSE
        CASE
          WHEN pop_density_class BETWEEN 1 AND 3 THEN 0.90
          WHEN pop_density_class BETWEEN 4 AND 6 THEN 0.95
          WHEN pop_density_class BETWEEN 7 AND 9 THEN 1.05
          WHEN pop_density_class BETWEEN 10 AND 12 THEN 1.15
          WHEN pop_density_class BETWEEN 13 AND 16 THEN 1.20
          ELSE 1.00
        END
    END AS pop_multiplier,
    
    -- Income 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,
    
    -- Property Value Multiplier
    CASE
      WHEN property_value_class IS NULL THEN 1.00
      ELSE
        CASE property_value_class
          WHEN 1 THEN 0.95
          WHEN 2 THEN 0.96
          WHEN 3 THEN 0.97
          WHEN 4 THEN 0.98
          WHEN 5 THEN 0.99
          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
    END AS property_value_multiplier,

    -- Allowed Variation Based on Income (TIGHTER version)
    CASE adm0_income_grp
      WHEN 'High income: OECD' THEN 0.15
      WHEN 'High income: nonOECD' THEN 0.17
      WHEN 'Upper middle income' THEN 0.20
      WHEN 'Lower middle income' THEN 0.22
      WHEN 'Low income' THEN 0.25
      ELSE 0.20
    END AS allowed_variation

  FROM base_risk
)

SELECT
  cell_id,
  country,
  year,
  base_risk_score,
  crime_multiplier,
  structure_multiplier,
  pop_multiplier,
  income_multiplier,
  property_value_multiplier,
  allowed_variation,

  -- Step: Raw unbounded score
  (base_risk_score *
   crime_multiplier *
   structure_multiplier *
   pop_multiplier *
   income_multiplier *
   property_value_multiplier
  ) AS total_risk_score_raw,

  -- Step: Final bounded score based on base risk ± allowed percent
  GREATEST(
    base_risk_score * (1 - allowed_variation),
    LEAST(
      base_risk_score * (1 + allowed_variation),
      (base_risk_score *
       crime_multiplier *
       structure_multiplier *
       pop_multiplier *
       income_multiplier *
       property_value_multiplier)
    )
  ) AS final_risk_score_bounded

FROM modifiers
ORDER BY final_risk_score_bounded DESC;