FULL MODEL:

WITH
main AS (SELECT * FROM candydata_main)

,combo_base AS ( /*create combinations and average their rates together*/
  SELECT m1.competitorname as candy_1,m2.competitorname as candy_2,m3.competitorname as candy_3
      ,(m1.chocolate + m2.chocolate + m3.chocolate) AS chocolate_all
      ,(m1.fruity + m2.fruity + m3.fruity) AS fruity_all
      ,(m1.caramel + m2.caramel + m3.caramel) AS caramel_all
      ,(m1.peanutyalmondy + m2.peanutyalmondy + m3.peanutyalmondy) AS peanutyalmondy_all
      ,(m1.nougat + m2.nougat + m3.nougat) AS nougat_all
      ,(m1.crispedricewafer + m2.crispedricewafer + m3.crispedricewafer) AS crispedricewafer_all
      ,(m1.hard + m2.hard + m3.hard) AS hard_all
      ,(m1.bar + m2.bar + m3.bar) AS bar_all
      ,(m1.pluribus + m2.pluribus + m3.pluribus) AS pluribus_all
  	  --,m1.winpercent as candy1_winr,m2.winpercent as candy2_winr,m3.winpercent as candy3_winr
      ,(m1.sugarpercent + m2.sugarpercent+ m3.sugarpercent)/3 as avg_sugarpercent
      ,(m1.pricepercent + m2.pricepercent+ m3.pricepercent)/3 as avg_pricepercent
  ,((m1.winpercent + m2.winpercent+ m3.winpercent)/3)/100 AS avg_winpercent

  FROM main m1
  CROSS JOIN main m2
  CROSS JOIN main m3
  WHERE m1.competitorname <> m2.competitorname
    AND m1.competitorname <> m3.competitorname
    AND m2.competitorname <> m3.competitorname
  )

,attributes_filtered AS ( /*filter combos that lack a category*/
  SELECT *
  		,1 AS gen_join
  FROM combo_base
  WHERE chocolate_all <> 0
    AND fruity_all <> 0 AND caramel_all <> 0
    AND peanutyalmondy_all <> 0 AND nougat_all <> 0
    AND crispedricewafer_all <> 0 AND hard_all <> 0
    AND bar_all <> 0 AND pluribus_all <> 0
  )
  
 ,percent_maxmins AS( /*get max-mins of rates for normalization purposes*/
    SELECT 1 AS gen_join
    	,MAX(avg_winpercent) AS max_winpercent
        ,MIN(avg_winpercent) AS min_winpercent
        ,MAX(avg_sugarpercent) AS max_sugarpercent
        ,MIN(avg_sugarpercent) AS min_sugarpercent
        ,MAX(avg_pricepercent) AS max_pricepercent
        ,MIN(avg_pricepercent) AS min_pricepercent
    FROM attributes_filtered
  )
  
 ,final AS ( /*normalize rates to get scores*/
  SELECT *
    ,(avg_winpercent-min_winpercent)/(max_winpercent-min_winpercent) AS normalized_win_score
    ,(max_sugarpercent-avg_sugarpercent)/(max_sugarpercent-min_sugarpercent) AS normalized_sugar_score
    ,(max_pricepercent-avg_pricepercent)/(max_pricepercent-min_pricepercent) AS normalized_price_score
  FROM attributes_filtered af
  LEFT JOIN percent_maxmins pm
  	ON af.gen_join=pm.gen_join
    )
  
,ranking AS (
  SELECT candy_1, candy_2, candy_3
  		,avg_winpercent, avg_sugarpercent, avg_pricepercent
  		,normalized_win_score,normalized_sugar_score,normalized_price_score
        ,ROW_NUMBER() OVER (ORDER BY normalized_win_score DESC) AS rank_overall
        ,ROW_NUMBER() OVER (ORDER BY (normalized_win_score + normalized_sugar_score) DESC) AS rank_sugar_optimal
        ,ROW_NUMBER() OVER (ORDER BY (normalized_win_score + normalized_price_score) DESC) AS rank_price_optimal
  FROM final
  ORDER BY normalized_win_score DESC 
    )
    
    SELECT * 
    FROM ranking
    WHERE rank_overall = 1
    or rank_sugar_optimal = 1
    or rank_price_optimal = 1

FINAL OUTPUT: