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:
candy_1 | candy_2 | candy_3 | avg_winpercent | avg_sugarpercent | avg_pricepercent | normalized_win_score | normalized_sugar_score | normalized_price_score | rank_overall | rank_sugar_optimal | rank_price_optimal |
---|---|---|---|---|---|---|---|---|---|---|---|
Nerds | Snickers | Twix | 0.712236 | 0.646667 | 0.627333 | 1 | 0.309091 | 0.488112 | 1 | 115 | 37 |
Kit Kat | Smarties candy | Snickers | 0.664794 | 0.375333 | 0.426 | 0.836481 | 0.878322 | 0.91049 | 43 | 1 | 1 |