ports_heroes (view)

12 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Jericho 574 354 220 61.67% 26.07% 57.70%
Jericho_StarCaptain 78 50 28 64.10% 3.54% 53.46%
Jericho_Highwayman 83 48 35 57.83% 3.77% 47.21%
Etienne 363 170 193 46.83% 16.49% 41.70%
Quincy_Cyber 264 125 139 47.35% 11.99% 41.33%
Quincy 255 120 135 47.06% 11.58% 40.93%
Etienne_Bee 161 74 87 45.96% 7.31% 38.26%
Obyn 61 27 34 44.26% 2.77% 31.80%
Churchill_Sentai 81 33 48 40.74% 3.68% 30.04%
Bonnie 77 31 46 40.26% 3.50% 29.31%
Adora 30 12 18 40.00% 1.36% 22.47%
Churchill 40 13 27 32.50% 1.82% 17.98%

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;