up_on_the_roof_heroes (view)

23 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Losses

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Gwendolin_Science 720 422 298 58.61% 12.78% 55.01%
Obyn_Ocean 189 116 73 61.38% 3.35% 54.43%
Jericho 231 135 96 58.44% 4.10% 52.09%
Bonnie 997 529 468 53.06% 17.70% 49.96%
Jericho_StarCaptain 92 53 39 57.61% 1.63% 47.51%
Adora 602 309 293 51.33% 10.69% 47.34%
PatFusty 134 74 60 55.22% 2.38% 46.80%
Gwendolin 199 101 98 50.75% 3.53% 43.81%
Benjamin 79 43 36 54.43% 1.40% 43.45%
StrikerJones_Biker 36 21 15 58.33% 0.64% 42.23%
PatFusty_Snowman 102 52 50 50.98% 1.81% 41.28%
Obyn 170 81 89 47.65% 3.02% 40.14%
Ezili_SmudgeCat 40 22 18 55.00% 0.71% 39.58%
Etienne_Bee 534 233 301 43.63% 9.48% 39.43%
Jericho_Highwayman 34 19 15 55.88% 0.60% 39.19%
Etienne 802 327 475 40.77% 14.24% 37.37%
Churchill 111 51 60 45.95% 1.97% 36.67%
Ezili 107 49 58 45.79% 1.90% 36.35%
Benjamin_DJ 84 38 46 45.24% 1.49% 34.59%
Quincy 192 68 124 35.42% 3.41% 28.65%
Quincy_Cyber 68 27 41 39.71% 1.21% 28.08%
Churchill_Sentai 57 23 34 40.35% 1.01% 27.61%
StrikerJones 39 16 23 41.03% 0.69% 25.59%

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;