ports_heroes (view)

23 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Jericho 3387 2139 1248 63.15% 21.31% 61.53%
Jericho_Highwayman 543 326 217 60.04% 3.42% 55.92%
Jericho_StarCaptain 475 276 199 58.11% 2.99% 53.67%
Quincy_Cyber 1374 683 691 49.71% 8.64% 47.07%
Etienne 3898 1849 2049 47.43% 24.52% 45.87%
Quincy 1577 760 817 48.19% 9.92% 45.73%
Bonnie 866 415 451 47.92% 5.45% 44.59%
Adora 306 143 163 46.73% 1.93% 41.14%
Etienne_Bee 847 371 476 43.80% 5.33% 40.46%
StrikerJones 176 81 95 46.02% 1.11% 38.66%
Obyn 322 139 183 43.17% 2.03% 37.76%
StrikerJones_Biker 207 89 118 43.00% 1.30% 36.25%
Ezili 127 56 71 44.09% 0.80% 35.46%
Churchill_Sentai 361 137 224 37.95% 2.27% 32.94%
PatFusty 136 54 82 39.71% 0.86% 31.48%
Adora_Fateweaver 94 37 57 39.36% 0.59% 29.49%
PatFusty_Snowman 93 36 57 38.71% 0.59% 28.81%
Gwendolin_Science 272 92 180 33.82% 1.71% 28.20%
Obyn_Ocean 130 46 84 35.38% 0.82% 27.16%
Churchill 282 89 193 31.56% 1.77% 26.14%
Benjamin 166 55 111 33.13% 1.04% 25.97%
Ezili_SmudgeCat 41 15 26 36.59% 0.26% 21.84%
Gwendolin 181 50 131 27.62% 1.14% 21.11%

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;