castle_ruins_heroes (view)

22 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Wins, Losses, Winrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Quincy_Cyber 405 232 173 57.28% 10.33% 52.47%
Etienne_Bee 500 273 227 54.60% 12.76% 50.24%
Obyn 483 253 230 52.38% 12.32% 47.93%
Jericho_StarCaptain 62 37 25 59.68% 1.58% 47.47%
Jericho 338 176 162 52.07% 8.62% 46.75%
Quincy 421 211 210 50.12% 10.74% 45.34%
Ezili 124 66 58 53.23% 3.16% 44.44%
Benjamin 130 68 62 52.31% 3.32% 43.72%
Adora_Fateweaver 32 19 13 59.38% 0.82% 42.36%
Jericho_Highwayman 116 58 58 50.00% 2.96% 40.90%
Etienne 347 159 188 45.82% 8.85% 40.58%
PatFusty_Snowman 96 48 48 50.00% 2.45% 40.00%
Ezili_SmudgeCat 38 21 17 55.26% 0.97% 39.45%
Churchill_Sentai 120 56 64 46.67% 3.06% 37.74%
Gwendolin_Science 128 55 73 42.97% 3.27% 34.39%
Gwendolin 115 49 66 42.61% 2.93% 33.57%
StrikerJones 61 28 33 45.90% 1.56% 33.40%
PatFusty 23 12 11 52.17% 0.59% 31.76%
Bonnie 173 62 111 35.84% 4.41% 28.69%
Adora 44 19 25 43.18% 1.12% 28.55%
Churchill 76 27 49 35.53% 1.94% 24.77%
Obyn_Ocean 52 16 36 30.77% 1.33% 18.22%

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;