up_on_the_roof_heroes (view)

24 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Wins, Losses, Winrate, Winrate_LowerBound_95CI

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
PatFusty_Snowman 107 68 39 63.55% 2.75% 54.43%
Gwendolin_Science 429 246 183 57.34% 11.04% 52.66%
Adora 481 258 223 53.64% 12.38% 49.18%
Bonnie 635 334 301 52.60% 16.34% 48.71%
Etienne_Bee 240 132 108 55.00% 6.18% 48.71%
Gwendolin 173 91 82 52.60% 4.45% 45.16%
Jericho_StarCaptain 38 23 15 60.53% 0.98% 44.98%
Obyn_Ocean 105 53 52 50.48% 2.70% 40.91%
Benjamin 191 91 100 47.64% 4.92% 40.56%
Churchill_Sentai 108 53 55 49.07% 2.78% 39.65%
Obyn 77 38 39 49.35% 1.98% 38.18%
Etienne 466 197 269 42.27% 11.99% 37.79%
Jericho 151 67 84 44.37% 3.89% 36.45%
Jericho_Highwayman 43 22 21 51.16% 1.11% 36.22%
Quincy_Cyber 64 31 33 48.44% 1.65% 36.19%
Quincy 204 86 118 42.16% 5.25% 35.38%
StrikerJones 41 19 22 46.34% 1.06% 31.08%
StrikerJones_Biker 35 16 19 45.71% 0.90% 29.21%
Ezili 67 27 40 40.30% 1.72% 28.55%
PatFusty 39 17 22 43.59% 1.00% 28.03%
Ezili_SmudgeCat 37 16 21 43.24% 0.95% 27.28%
Benjamin_DJ 24 11 13 45.83% 0.62% 25.90%
Adora_Fateweaver 27 12 15 44.44% 0.69% 25.70%
Churchill 104 35 69 33.65% 2.68% 24.57%

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;