-- Top 20 Fragrances (Weighted Rating)
DECLARE @C FLOAT = (
    SELECTSELECT AVG(CAST(rating_value ASAS FLOAT))
    FROMFROM perfumes
    WHEREWHERE rating_value IS NOTNOT NULL
);
DECLARE @m FLOAT = (
    SELECTSELECT PERCENTILE_CONT(0.9) 
           WITHIN GROUP (ORDER BYORDER BY rating_count) OVER ()
    FROMFROM perfumes
    WHEREWHERE rating_count IS NOTNOT NULL
);
SELECTSELECT TOP 20
    perfume_id, perfume, brand,
    rating_value ASAS R, rating_count ASAS v,
    ROUND(
        (rating_count * 1.0 / (rating_count + @m)) * rating_value +
        (@m / (rating_count + @m)) * @C
    , 2) ASAS weighted_rating
FROMFROM perfumes
WHEREWHERE rating_count IS NOTNOT NULL
ORDER BYORDER BY weighted_rating DESC;