castle_ruins_heroes (view)

24 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Losses, Pickrate, Winrate_LowerBound_95CI

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Jericho_Highwayman 88 60 28 68.18% 2.04% 58.45%
PatFusty_Snowman 147 97 50 65.99% 3.41% 58.33%
Quincy 252 146 106 57.94% 5.84% 51.84%
Obyn 403 225 178 55.83% 9.34% 50.98%
Jericho 236 131 105 55.51% 5.47% 49.17%
Adora_Fateweaver 49 30 19 61.22% 1.14% 47.58%
PatFusty 75 44 31 58.67% 1.74% 47.52%
Adora 157 84 73 53.50% 3.64% 45.70%
Etienne_Bee 472 237 235 50.21% 10.94% 45.70%
Etienne 888 433 455 48.76% 20.58% 45.47%
Benjamin_DJ 34 21 13 61.76% 0.79% 45.43%
Ezili 106 54 52 50.94% 2.46% 41.43%
Ezili_SmudgeCat 49 27 22 55.10% 1.14% 41.18%
Gwendolin_Science 378 164 214 43.39% 8.76% 38.39%
Jericho_StarCaptain 75 37 38 49.33% 1.74% 38.02%
Quincy_Cyber 108 51 57 47.22% 2.50% 37.81%
Bonnie 337 139 198 41.25% 7.81% 35.99%
StrikerJones 24 13 11 54.17% 0.56% 34.23%
StrikerJones_Biker 43 20 23 46.51% 1.00% 31.60%
Churchill 60 25 35 41.67% 1.39% 29.19%
Benjamin 63 26 37 41.27% 1.46% 29.11%
Churchill_Sentai 41 18 23 43.90% 0.95% 28.71%
Obyn_Ocean 88 33 55 37.50% 2.04% 27.38%
Gwendolin 141 42 99 29.79% 3.27% 22.24%

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;