up_on_the_roof_heroes (view)

20 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Wins, Losses, Pickrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Adora 187 121 66 64.71% 8.01% 57.86%
Bonnie 289 157 132 54.33% 12.38% 48.58%
StrikerJones_Biker 94 55 39 58.51% 4.03% 48.55%
Gwendolin_Science 148 80 68 54.05% 6.34% 46.03%
PatFusty 29 18 11 62.07% 1.24% 44.41%
Jericho 106 57 49 53.77% 4.54% 44.28%
Obyn_Ocean 80 42 38 52.50% 3.43% 41.56%
Quincy_Cyber 164 80 84 48.78% 7.03% 41.13%
Gwendolin 75 39 36 52.00% 3.21% 40.69%
Benjamin 80 41 39 51.25% 3.43% 40.30%
Quincy 177 83 94 46.89% 7.58% 39.54%
Etienne 320 139 181 43.44% 13.71% 38.01%
Etienne_Bee 131 58 73 44.27% 5.61% 35.77%
Jericho_StarCaptain 30 16 14 53.33% 1.29% 35.48%
Churchill_Sentai 92 41 51 44.57% 3.94% 34.41%
Jericho_Highwayman 42 20 22 47.62% 1.80% 32.51%
Obyn 59 26 33 44.07% 2.53% 31.40%
Churchill 118 46 72 38.98% 5.06% 30.18%
Ezili 25 12 13 48.00% 1.07% 28.42%
StrikerJones 44 16 28 36.36% 1.89% 22.15%

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;