salmon_pool_heroes (view)

19 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
Churchill_Sentai 668 386 282 57.78% 22.15% 54.04%
Obyn 102 60 42 58.82% 3.38% 49.27%
Bonnie 430 228 202 53.02% 14.26% 48.31%
Benjamin 101 58 43 57.43% 3.35% 47.78%
Quincy 266 135 131 50.75% 8.82% 44.74%
Quincy_Cyber 278 140 138 50.36% 9.22% 44.48%
Ezili 59 31 28 52.54% 1.96% 39.80%
Churchill 188 82 106 43.62% 6.23% 36.53%
Etienne_Bee 147 63 84 42.86% 4.87% 34.86%
PatFusty 33 17 16 51.52% 1.09% 34.46%
Jericho 165 69 96 41.82% 5.47% 34.29%
Etienne 256 103 153 40.23% 8.49% 34.23%
StrikerJones_Biker 36 17 19 47.22% 1.19% 30.91%
Adora 39 18 21 46.15% 1.29% 30.51%
Gwendolin 59 25 34 42.37% 1.96% 29.76%
Jericho_Highwayman 23 11 12 47.83% 0.76% 27.41%
Gwendolin_Science 43 18 25 41.86% 1.43% 27.11%
Obyn_Ocean 27 11 16 40.74% 0.90% 22.21%
PatFusty_Snowman 28 10 18 35.71% 0.93% 17.97%

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;