times_up_heroes (view)

20 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Wins, Losses, Pickrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
PatFusty_Snowman 208 131 77 62.98% 8.38% 56.42%
Adora_Fateweaver 62 39 23 62.90% 2.50% 50.88%
Quincy_Cyber 532 289 243 54.32% 21.43% 50.09%
Jericho_StarCaptain 32 21 11 65.63% 1.29% 49.17%
StrikerJones 98 56 42 57.14% 3.95% 47.34%
Jericho_Highwayman 47 28 19 59.57% 1.89% 45.54%
Etienne_Bee 237 120 117 50.63% 9.55% 44.27%
Quincy 288 140 148 48.61% 11.60% 42.84%
Etienne 215 98 117 45.58% 8.66% 38.92%
Adora 57 29 28 50.88% 2.30% 37.90%
Gwendolin 113 53 60 46.90% 4.55% 37.70%
Gwendolin_Science 64 31 33 48.44% 2.58% 36.19%
PatFusty 83 37 46 44.58% 3.34% 33.88%
Jericho 123 49 74 39.84% 4.96% 31.19%
Benjamin 28 13 15 46.43% 1.13% 27.96%
Obyn 32 14 18 43.75% 1.29% 26.56%
Bonnie 73 27 46 36.99% 2.94% 25.91%
Ezili 64 21 43 32.81% 2.58% 21.31%
Churchill_Sentai 28 11 17 39.29% 1.13% 21.20%
StrikerJones_Biker 34 11 23 32.35% 1.37% 16.63%

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;