ports_heroes (view)

13 rows

✖

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Wins

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Jericho_StarCaptain 68 49 19 72.06% 3.28% 61.39%
Jericho 320 202 118 63.13% 15.43% 57.84%
Jericho_Highwayman 69 46 23 66.67% 3.33% 55.54%
Quincy 184 97 87 52.72% 8.87% 45.50%
Etienne 705 345 360 48.94% 33.99% 45.25%
Bonnie 150 66 84 44.00% 7.23% 36.06%
Adora 35 18 17 51.43% 1.69% 34.87%
Etienne_Bee 174 73 101 41.95% 8.39% 34.62%
Churchill_Sentai 40 19 21 47.50% 1.93% 32.02%
Quincy_Cyber 45 19 26 42.22% 2.17% 27.79%
Obyn 26 11 15 42.31% 1.25% 23.32%
Gwendolin_Science 54 18 36 33.33% 2.60% 20.76%
Gwendolin 34 12 22 35.29% 1.64% 19.23%

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;