Prioritization and Stakeholder Alignment Questions

WITH metrics AS (
  -- attach per-request_type min, max and median for each metric
  SELECT
    r.*,
    MIN(business_value)    OVER (PARTITION BY request_type) AS mv_min,
    MAX(business_value)    OVER (PARTITION BY request_type) AS mv_max,
    MIN(complexity)        OVER (PARTITION BY request_type) AS c_min,
    MAX(complexity)        OVER (PARTITION BY request_type) AS c_max,
    MIN(strategic_fit)     OVER (PARTITION BY request_type) AS sf_min,
    MAX(strategic_fit)     OVER (PARTITION BY request_type) AS sf_max,
    MIN(confidence)        OVER (PARTITION BY request_type) AS cf_min,
    MAX(confidence)        OVER (PARTITION BY request_type) AS cf_max,
    -- median via percentile_cont (ANSI SQL)
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY business_value)
      OVER (PARTITION BY request_type) AS mv_median,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY complexity)
      OVER (PARTITION BY request_type) AS c_median,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY strategic_fit)
      OVER (PARTITION BY request_type) AS sf_median,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY confidence)
      OVER (PARTITION BY request_type) AS cf_median
  FROM requests r
),
imputed AS (
  -- replace NULLs with group median
  SELECT
    id,
    request_type,
    COALESCE(business_value, mv_median)    AS business_value_imputed,
    COALESCE(complexity, c_median)         AS complexity_imputed,
    COALESCE(strategic_fit, sf_median)     AS strategic_fit_imputed,
    COALESCE(confidence, cf_median)        AS confidence_imputed,
    mv_min, mv_max, c_min, c_max, sf_min, sf_max, cf_min, cf_max
  FROM metrics
),
normalized AS (
  SELECT
    id,
    -- cast to numeric to ensure decimal division
    CASE
      WHEN mv_max IS NULL OR mv_min IS NULL THEN 0.0
      ELSE (CAST(business_value_imputed AS NUMERIC) - CAST(mv_min AS NUMERIC))
           / NULLIF(CAST(mv_max AS NUMERIC) - CAST(mv_min AS NUMERIC), 0)
    END AS business_value_norm,
    CASE
      WHEN c_max IS NULL OR c_min IS NULL THEN 0.0
      ELSE (CAST(complexity_imputed AS NUMERIC) - CAST(c_min AS NUMERIC))
           / NULLIF(CAST(c_max AS NUMERIC) - CAST(c_min AS NUMERIC), 0)
    END AS complexity_norm,
    CASE
      WHEN sf_max IS NULL OR sf_min IS NULL THEN 0.0
      ELSE (CAST(strategic_fit_imputed AS NUMERIC) - CAST(sf_min AS NUMERIC))
           / NULLIF(CAST(sf_max AS NUMERIC) - CAST(sf_min AS NUMERIC), 0)
    END AS strategic_fit_norm,
    CASE
      WHEN cf_max IS NULL OR cf_min IS NULL THEN 0.0
      ELSE (CAST(confidence_imputed AS NUMERIC) - CAST(cf_min AS NUMERIC))
           / NULLIF(CAST(cf_max AS NUMERIC) - CAST(cf_min AS NUMERIC), 0)
    END AS confidence_norm
  FROM imputed
)
SELECT
  id,
  -- keep normalized components bounded [0,1] where possible
  ROUND(CASE WHEN business_value_norm IS NULL THEN 0.0 ELSE business_value_norm END, 6)   AS business_value_norm,
  ROUND(CASE WHEN complexity_norm IS NULL     THEN 0.0 ELSE complexity_norm END, 6)       AS complexity_norm,
  ROUND(CASE WHEN strategic_fit_norm IS NULL  THEN 0.0 ELSE strategic_fit_norm END, 6)    AS strategic_fit_norm,
  ROUND(CASE WHEN confidence_norm IS NULL     THEN 0.0 ELSE confidence_norm END, 6)       AS confidence_norm,
  -- final weighted score: weights 0.4, -0.2, 0.3, 0.1
  ROUND(
    0.4  * COALESCE(business_value_norm, 0.0)
  + (-0.2) * COALESCE(complexity_norm, 0.0)
  + 0.3  * COALESCE(strategic_fit_norm, 0.0)
  + 0.1  * COALESCE(confidence_norm, 0.0)
  , 6) AS final_score
FROM normalized
ORDER BY id;