up_on_the_roof_heroes (view)

24 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Gwendolin_Science 3234 1874 1360 57.95% 10.09% 56.25%
PatFusty_Snowman 565 331 234 58.58% 1.76% 54.52%
Jericho_StarCaptain 471 270 201 57.32% 1.47% 52.86%
Adora 3724 2008 1716 53.92% 11.62% 52.32%
Obyn_Ocean 1018 556 462 54.62% 3.18% 51.56%
Bonnie 5633 2931 2702 52.03% 17.58% 50.73%
StrikerJones_Biker 282 159 123 56.38% 0.88% 50.59%
PatFusty 419 224 195 53.46% 1.31% 48.68%
Benjamin_DJ 417 221 196 53.00% 1.30% 48.21%
Jericho_Highwayman 353 187 166 52.97% 1.10% 47.77%
Jericho 1271 637 634 50.12% 3.97% 47.37%
Gwendolin 1313 636 677 48.44% 4.10% 45.74%
Etienne_Bee 1795 844 951 47.02% 5.60% 44.71%
Benjamin 927 442 485 47.68% 2.89% 44.47%
Ezili 645 310 335 48.06% 2.01% 44.21%
Obyn 725 340 385 46.90% 2.26% 43.26%
Quincy 1428 637 791 44.61% 4.46% 42.03%
Ezili_SmudgeCat 263 126 137 47.91% 0.82% 41.87%
Quincy_Cyber 801 362 439 45.19% 2.50% 41.75%
Churchill 944 419 525 44.39% 2.95% 41.22%
Churchill_Sentai 830 370 460 44.58% 2.59% 41.20%
Etienne 4494 1916 2578 42.63% 14.03% 41.19%
StrikerJones 305 136 169 44.59% 0.95% 39.01%
Adora_Fateweaver 181 83 98 45.86% 0.56% 38.60%

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;