times_up_heroes (view)

19 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Wins, Losses, Winrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
StrikerJones 66 44 22 66.67% 2.62% 55.29%
Adora_Fateweaver 96 61 35 63.54% 3.81% 53.91%
Jericho_StarCaptain 36 24 12 66.67% 1.43% 51.27%
Quincy_Cyber 419 229 190 54.65% 16.64% 49.89%
PatFusty_Snowman 233 128 105 54.94% 9.25% 48.55%
Etienne_Bee 370 192 178 51.89% 14.69% 46.80%
Jericho 143 78 65 54.55% 5.68% 46.38%
Quincy 301 150 151 49.83% 11.95% 44.19%
PatFusty 77 42 35 54.55% 3.06% 43.42%
Ezili 91 43 48 47.25% 3.61% 37.00%
Gwendolin 99 42 57 42.42% 3.93% 32.69%
Etienne 241 92 149 38.17% 9.57% 32.04%
Gwendolin_Science 64 27 37 42.19% 2.54% 30.09%
Jericho_Highwayman 32 15 17 46.88% 1.27% 29.58%
Adora 31 14 17 45.16% 1.23% 27.64%
Obyn 35 14 21 40.00% 1.39% 23.77%
StrikerJones_Biker 24 10 14 41.67% 0.95% 21.94%
Churchill 25 10 15 40.00% 0.99% 20.80%
Bonnie 60 18 42 30.00% 2.38% 18.40%

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;