splashdown_heroes (view)

7 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Wins

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Quincy_Cyber 228 136 92 59.65% 32.76% 53.28%
Bonnie 138 60 78 43.48% 19.83% 35.21%
Jericho 22 12 10 54.55% 3.16% 33.74%
Etienne 61 27 34 44.26% 8.76% 31.80%
Adora 33 16 17 48.48% 4.74% 31.43%
Etienne_Bee 34 13 21 38.24% 4.89% 21.90%
Quincy 45 16 29 35.56% 6.47% 21.57%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

CREATE VIEW splashdown_heroes AS 
WITH splashdown AS
    (SELECT *
    FROM matches
    WHERE map = 'splashdown')
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 splashdown) * 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 splashdown
                  UNION ALL
                  SELECT rHero             AS Hero,
                         NOT playerLeftWin AS Win
                  FROM splashdown)
            GROUP BY Hero))
WHERE Wins >= 10 AND Losses >= 10 AND LENGTH(Winrate_LowerBound_95CI) = 6
ORDER BY Winrate_LowerBound_95CI DESC;