salmon_pool_heroes (view)

16 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Wins, Losses, Pickrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Benjamin 134 83 51 61.94% 3.53% 53.72%
Churchill_Sentai 745 420 325 56.38% 19.63% 52.81%
Jericho 100 61 39 61.00% 2.63% 51.44%
Bonnie 861 448 413 52.03% 22.68% 48.70%
Quincy 322 162 160 50.31% 8.48% 44.85%
Quincy_Cyber 116 62 54 53.45% 3.06% 44.37%
Adora 68 36 32 52.94% 1.79% 41.08%
Churchill 376 173 203 46.01% 9.91% 40.97%
PatFusty_Snowman 63 32 31 50.79% 1.66% 38.45%
Etienne 366 152 214 41.53% 9.64% 36.48%
Ezili 65 30 35 46.15% 1.71% 34.03%
Etienne_Bee 177 68 109 38.42% 4.66% 31.25%
Jericho_Highwayman 37 17 20 45.95% 0.97% 29.89%
Obyn 82 33 49 40.24% 2.16% 29.63%
Gwendolin 74 30 44 40.54% 1.95% 29.35%
Gwendolin_Science 68 17 51 25.00% 1.79% 14.71%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

CREATE VIEW salmon_pool_heroes AS 
WITH salmon_pool AS
    (SELECT *
    FROM matches
    WHERE map = 'salmon_pool')
SELECT Hero,
       Games,
       Wins,
       Losses,
       printf('%.2f%%', WR * 100)                                          AS Winrate,
       printf('%.2f%%', PR * 100)                                          AS Pickrate,
       printf('%.2f%%', (WR - 1.96 * SQRT((WR * (1 - WR)) / Games)) * 100) AS Winrate_LowerBound_95CI
FROM (SELECT Hero,
             Games,
             Wins,
             Losses,
             CAST(Wins AS REAL) / Games AS WR,
             CAST(Games AS REAL) / (
                 (SELECT COUNT(*)
                  FROM salmon_pool) * 2)    AS PR
      FROM (SELECT Hero,
                   SUM(Win) + SUM(NOT Win) AS Games,
                   SUM(Win)                AS Wins,
                   SUM(NOT Win)            AS Losses
            FROM (SELECT lHero             AS Hero,
                         playerLeftWin     AS Win
                  FROM salmon_pool
                  UNION ALL
                  SELECT rHero             AS Hero,
                         NOT playerLeftWin AS Win
                  FROM salmon_pool)
            GROUP BY Hero))
WHERE Wins >= 10 AND Losses >= 10 AND LENGTH(Winrate_LowerBound_95CI) = 6
ORDER BY Winrate_LowerBound_95CI DESC;