castle_ruins_heroes (view)

23 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Wins, Losses, Winrate_LowerBound_95CI

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Quincy_Cyber 687 438 249 63.76% 15.88% 60.16%
Benjamin 280 166 114 59.29% 6.47% 53.53%
PatFusty_Snowman 127 74 53 58.27% 2.94% 49.69%
Jericho 351 185 166 52.71% 8.11% 47.48%
Jericho_StarCaptain 81 47 34 58.02% 1.87% 47.28%
Obyn 256 133 123 51.95% 5.92% 45.83%
Etienne_Bee 341 172 169 50.44% 7.88% 45.13%
Ezili 58 32 26 55.17% 1.34% 42.37%
Quincy 364 170 194 46.70% 8.41% 41.58%
StrikerJones_Biker 204 97 107 47.55% 4.72% 40.70%
Etienne 482 216 266 44.81% 11.14% 40.37%
Bonnie 232 102 130 43.97% 5.36% 37.58%
Gwendolin 118 52 66 44.07% 2.73% 35.11%
Jericho_Highwayman 93 40 53 43.01% 2.15% 32.95%
Adora_Fateweaver 38 18 20 47.37% 0.88% 31.49%
Adora 72 30 42 41.67% 1.66% 30.28%
Churchill_Sentai 106 39 67 36.79% 2.45% 27.61%
PatFusty 66 26 40 39.39% 1.53% 27.61%
Churchill 85 32 53 37.65% 1.96% 27.35%
Gwendolin_Science 97 35 62 36.08% 2.24% 26.53%
Obyn_Ocean 79 25 54 31.65% 1.83% 21.39%
Ezili_SmudgeCat 28 11 17 39.29% 0.65% 21.20%
StrikerJones 64 17 47 26.56% 1.48% 15.74%

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;