Top Brandsの実行結果

SQLの説明

Used score metric - median of popularity_boosted_score.

検索結果

実行したSQL

            WITH params AS (
    SELECT 70.0::numeric AS global_avg, 60::numeric AS m
),
filtered_games AS (
    SELECT
        game_id,
        gamename,
        average,
        median,
        count,
        allcount,
        model,
        brandname,
        brand_id
    FROM
        toukei_temp_table
    WHERE
        sellday <= current_date
        AND count >= 20
        AND allcount > 0
),
scored_games AS (
    SELECT
        f.*,
        ROUND((((count * average + p.m * p.global_avg) / (count + p.m)) + LOG(10, allcount))::numeric, 4) AS popularity_boosted_score
    FROM filtered_games f, params p
),

final as(
SELECT
    '<a href="brand.php?brand=' || brand_id || '">' || brandname || '</a>' AS "Brand name",
    percentile_cont(0.5) WITHIN GROUP (ORDER BY popularity_boosted_score) AS "median popularity_boosted_score",
    Count (popularity_boosted_score) as "Score count"
FROM scored_games
GROUP BY scored_games.brand_id, scored_games.brandname
HAVING Count (popularity_boosted_score) > 2
)

Select
ROW_NUMBER() OVER (ORDER BY "median popularity_boosted_score" DESC) AS rank, 
*
From final
limit 100