times_up_heroes (view)

19 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Wins

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
PatFusty_Snowman 615 384 231 62.44% 18.97% 58.61%
Adora_Fateweaver 218 136 82 62.39% 6.72% 55.95%
Jericho_StarCaptain 59 37 22 62.71% 1.82% 50.37%
Quincy_Cyber 153 89 64 58.17% 4.72% 50.35%
Etienne 404 198 206 49.01% 12.46% 44.14%
Quincy 417 199 218 47.72% 12.86% 42.93%
Etienne_Bee 369 172 197 46.61% 11.38% 41.52%
Bonnie 91 46 45 50.55% 2.81% 40.28%
Jericho_Highwayman 44 24 20 54.55% 1.36% 39.83%
Ezili 147 61 86 41.50% 4.53% 33.53%
Gwendolin_Science 150 62 88 41.33% 4.63% 33.45%
Benjamin 26 13 13 50.00% 0.80% 30.78%
Gwendolin 113 42 71 37.17% 3.49% 28.26%
Jericho 117 43 74 36.75% 3.61% 28.02%
PatFusty 75 28 47 37.33% 2.31% 26.39%
Adora 57 22 35 38.60% 1.76% 25.96%
Obyn_Ocean 25 11 14 44.00% 0.77% 24.54%
StrikerJones 37 13 24 35.14% 1.14% 19.75%
Obyn 53 16 37 30.19% 1.63% 17.83%

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;