times_up_heroes (view)

18 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Losses, Winrate, Pickrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
PatFusty_Snowman 635 401 234 63.15% 20.54% 59.40%
Adora_Fateweaver 178 115 63 64.61% 5.76% 57.58%
Etienne_Bee 207 109 98 52.66% 6.69% 45.86%
Quincy 283 145 138 51.24% 9.15% 45.41%
PatFusty 100 53 47 53.00% 3.23% 43.22%
Jericho 202 99 103 49.01% 6.53% 42.12%
StrikerJones 88 44 44 50.00% 2.85% 39.55%
Ezili 101 48 53 47.52% 3.27% 37.79%
Etienne 504 212 292 42.06% 16.30% 37.75%
Gwendolin 144 63 81 43.75% 4.66% 35.65%
Jericho_StarCaptain 42 21 21 50.00% 1.36% 34.88%
Adora 92 39 53 42.39% 2.98% 32.29%
Quincy_Cyber 71 31 40 43.66% 2.30% 32.13%
Gwendolin_Science 138 55 83 39.86% 4.46% 31.69%
Bonnie 92 37 55 40.22% 2.98% 30.20%
Benjamin 33 15 18 45.45% 1.07% 28.47%
Jericho_Highwayman 38 16 22 42.11% 1.23% 26.41%
Obyn 53 18 35 33.96% 1.71% 21.21%

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;