ports_heroes (view)

14 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Wins, Winrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Jericho 600 394 206 65.67% 28.28% 61.87%
Jericho_Highwayman 67 38 29 56.72% 3.16% 44.85%
Quincy_Cyber 136 72 64 52.94% 6.41% 44.55%
Etienne 456 204 252 44.74% 21.49% 40.17%
Jericho_StarCaptain 55 29 26 52.73% 2.59% 39.53%
Quincy 237 108 129 45.57% 11.17% 39.23%
Etienne_Bee 119 50 69 42.02% 5.61% 33.15%
Bonnie 101 41 60 40.59% 4.76% 31.02%
Churchill_Sentai 63 26 37 41.27% 2.97% 29.11%
Churchill 49 21 28 42.86% 2.31% 29.00%
Adora_Fateweaver 20 10 10 50.00% 0.94% 28.09%
Ezili 28 12 16 42.86% 1.32% 24.53%
Adora 33 12 21 36.36% 1.56% 19.95%
Benjamin 41 14 27 34.15% 1.93% 19.63%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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