castle_ruins_heroes (view)

22 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Wins, Losses, Winrate, Pickrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Jericho 329 187 142 56.84% 8.32% 51.49%
Quincy 280 156 124 55.71% 7.09% 49.90%
Obyn 348 187 161 53.74% 8.81% 48.50%
Adora_Fateweaver 63 37 26 58.73% 1.59% 46.57%
Gwendolin_Science 299 156 143 52.17% 7.57% 46.51%
Benjamin 255 134 121 52.55% 6.45% 46.42%
StrikerJones 79 44 35 55.70% 2.00% 44.74%
Bonnie 242 121 121 50.00% 6.12% 43.70%
Jericho_StarCaptain 79 43 36 54.43% 2.00% 43.45%
PatFusty_Snowman 198 99 99 50.00% 5.01% 43.04%
Etienne 701 327 374 46.65% 17.74% 42.95%
Etienne_Bee 273 129 144 47.25% 6.91% 41.33%
Quincy_Cyber 81 42 39 51.85% 2.05% 40.97%
Gwendolin 144 70 74 48.61% 3.64% 40.45%
Ezili 91 46 45 50.55% 2.30% 40.28%
Adora 141 64 77 45.39% 3.57% 37.17%
PatFusty 47 23 24 48.94% 1.19% 34.64%
StrikerJones_Biker 29 15 14 51.72% 0.73% 33.54%
Churchill 60 26 34 43.33% 1.52% 30.79%
Jericho_Highwayman 42 16 26 38.10% 1.06% 23.41%
Churchill_Sentai 73 24 49 32.88% 1.85% 22.10%
Obyn_Ocean 59 17 42 28.81% 1.49% 17.26%

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;