garden_heroes (view)

10 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Losses

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Quincy_Cyber 210 128 82 60.95% 24.65% 54.35%
Etienne_Bee 77 45 32 58.44% 9.04% 47.43%
Benjamin 35 22 13 62.86% 4.11% 46.85%
Churchill 81 43 38 53.09% 9.51% 42.22%
Adora 24 14 10 58.33% 2.82% 38.61%
Churchill_Sentai 74 31 43 41.89% 8.69% 30.65%
Quincy 63 27 36 42.86% 7.39% 30.64%
Etienne 102 39 63 38.24% 11.97% 28.80%
Bonnie 52 20 32 38.46% 6.10% 25.24%
Obyn 28 10 18 35.71% 3.29% 17.97%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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