up_on_the_roof_heroes (view)

20 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Losses, Pickrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
StrikerJones_Biker 47 32 15 68.09% 2.20% 54.76%
Gwendolin_Science 148 85 63 57.43% 6.92% 49.47%
Churchill_Sentai 105 59 46 56.19% 4.91% 46.70%
Jericho_Highwayman 32 20 12 62.50% 1.50% 45.73%
Quincy 145 78 67 53.79% 6.78% 45.68%
Adora 188 97 91 51.60% 8.79% 44.45%
Churchill 63 34 29 53.97% 2.95% 41.66%
Bonnie 305 143 162 46.89% 14.27% 41.28%
PatFusty_Snowman 27 16 11 59.26% 1.26% 40.73%
Quincy_Cyber 139 67 72 48.20% 6.50% 39.89%
Gwendolin 81 41 40 50.62% 3.79% 39.73%
Etienne 259 115 144 44.40% 12.11% 38.35%
Etienne_Bee 175 80 95 45.71% 8.19% 38.33%
Jericho 103 49 54 47.57% 4.82% 37.93%
Obyn_Ocean 62 30 32 48.39% 2.90% 35.95%
Obyn 41 21 20 51.22% 1.92% 35.92%
Benjamin 57 25 32 43.86% 2.67% 30.98%
Jericho_StarCaptain 29 14 15 48.28% 1.36% 30.09%
Ezili 57 24 33 42.11% 2.67% 29.29%
StrikerJones 22 11 11 50.00% 1.03% 29.11%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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