times_up_heroes (view)

24 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Adora_Fateweaver 1214 786 428 64.74% 5.29% 62.06%
PatFusty_Snowman 2469 1503 966 60.87% 10.77% 58.95%
StrikerJones 519 293 226 56.45% 2.26% 52.19%
Quincy_Cyber 1921 1022 899 53.20% 8.38% 50.97%
Etienne_Bee 2145 1106 1039 51.56% 9.36% 49.45%
Quincy 2333 1182 1151 50.66% 10.18% 48.64%
PatFusty 640 332 308 51.88% 2.79% 48.00%
Etienne 3614 1697 1917 46.96% 15.76% 45.33%
Jericho_StarCaptain 370 186 184 50.27% 1.61% 45.18%
Adora 673 325 348 48.29% 2.94% 44.52%
Gwendolin 1147 541 606 47.17% 5.00% 44.28%
Ezili 923 423 500 45.83% 4.03% 42.61%
Jericho 1195 535 660 44.77% 5.21% 41.95%
Jericho_Highwayman 301 143 158 47.51% 1.31% 41.87%
Gwendolin_Science 1069 479 590 44.81% 4.66% 41.83%
StrikerJones_Biker 129 65 64 50.39% 0.56% 41.76%
Ezili_SmudgeCat 99 48 51 48.48% 0.43% 38.64%
Bonnie 857 334 523 38.97% 3.74% 35.71%
Obyn_Ocean 236 94 142 39.83% 1.03% 33.58%
Benjamin 200 78 122 39.00% 0.87% 32.24%
Obyn 425 156 269 36.71% 1.85% 32.12%
Churchill_Sentai 201 64 137 31.84% 0.88% 25.40%
Churchill 209 61 148 29.19% 0.91% 23.02%
Benjamin_DJ 39 11 28 28.21% 0.17% 14.08%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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