castle_ruins_heroes (view)

24 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
Jericho 386 223 163 57.77% 8.32% 52.84%
Ezili 110 67 43 60.91% 2.37% 51.79%
Adora_Fateweaver 69 43 26 62.32% 1.49% 50.88%
Etienne_Bee 399 212 187 53.13% 8.60% 48.24%
Jericho_StarCaptain 119 67 52 56.30% 2.56% 47.39%
PatFusty_Snowman 125 69 56 55.20% 2.69% 46.48%
Benjamin_DJ 98 55 43 56.12% 2.11% 46.30%
Quincy_Cyber 150 81 69 54.00% 3.23% 46.02%
Gwendolin_Science 360 181 179 50.28% 7.76% 45.11%
Obyn 368 184 184 50.00% 7.93% 44.89%
Benjamin 207 107 100 51.69% 4.46% 44.88%
Jericho_Highwayman 69 39 30 56.52% 1.49% 44.82%
Adora 171 86 85 50.29% 3.69% 42.80%
Quincy 215 104 111 48.37% 4.63% 41.69%
Etienne 890 400 490 44.94% 19.18% 41.68%
Bonnie 349 158 191 45.27% 7.52% 40.05%
StrikerJones 61 32 29 52.46% 1.31% 39.93%
PatFusty 65 32 33 49.23% 1.40% 37.08%
Gwendolin 128 56 72 43.75% 2.76% 35.16%
Obyn_Ocean 100 44 56 44.00% 2.16% 34.27%
Churchill 86 35 51 40.70% 1.85% 30.31%
Ezili_SmudgeCat 28 13 15 46.43% 0.60% 27.96%
Churchill_Sentai 63 22 41 34.92% 1.36% 23.15%
StrikerJones_Biker 24 10 14 41.67% 0.52% 21.94%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

CREATE VIEW castle_ruins_heroes AS 
WITH castle_ruins AS
    (SELECT *
    FROM matches
    WHERE map = 'castle_ruins')
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 castle_ruins) * 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 castle_ruins
                  UNION ALL
                  SELECT rHero             AS Hero,
                         NOT playerLeftWin AS Win
                  FROM castle_ruins)
            GROUP BY Hero))
WHERE Wins >= 10 AND Losses >= 10 AND LENGTH(Winrate_LowerBound_95CI) = 6
ORDER BY Winrate_LowerBound_95CI DESC;