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 429 275 154 64.10% 24.05% 59.56%
Jericho_Highwayman 83 48 35 57.83% 4.65% 47.21%
Quincy 170 88 82 51.76% 9.53% 44.25%
Etienne 431 198 233 45.94% 24.16% 41.23%
Bonnie 98 50 48 51.02% 5.49% 41.12%
Etienne_Bee 75 37 38 49.33% 4.20% 38.02%
Jericho_StarCaptain 65 32 33 49.23% 3.64% 37.08%
Quincy_Cyber 63 29 34 46.03% 3.53% 33.72%
Adora 49 21 28 42.86% 2.75% 29.00%
PatFusty_Snowman 29 13 16 44.83% 1.63% 26.73%
Churchill 33 11 22 33.33% 1.85% 17.25%
Churchill_Sentai 42 13 29 30.95% 2.35% 16.97%
Gwendolin_Science 35 11 24 31.43% 1.96% 16.05%

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;