ports_heroes (view)

6 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Jericho 94 64 30 68.09% 18.43% 58.66%
Quincy_Cyber 144 73 71 50.69% 28.24% 42.53%
Etienne 71 34 37 47.89% 13.92% 36.27%
StrikerJones_Biker 21 11 10 52.38% 4.12% 31.02%
Bonnie 22 10 12 45.45% 4.31% 24.65%
Quincy 41 14 27 34.15% 8.04% 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;