basalt_columns_heroes (view)

14 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Wins, Losses, Pickrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
PatFusty_Snowman 58 41 17 70.69% 2.88% 58.97%
Adora 78 49 29 62.82% 3.87% 52.10%
Quincy_Cyber 555 296 259 53.33% 27.56% 49.18%
Benjamin 121 69 52 57.02% 6.01% 48.20%
Jericho 125 71 54 56.80% 6.21% 48.12%
Quincy 224 116 108 51.79% 11.12% 45.24%
Etienne 270 135 135 50.00% 13.41% 44.04%
Obyn 39 23 16 58.97% 1.94% 43.54%
Bonnie 100 44 56 44.00% 4.97% 34.27%
Etienne_Bee 106 45 61 42.45% 5.26% 33.04%
StrikerJones_Biker 78 29 49 37.18% 3.87% 26.45%
Churchill 44 15 29 34.09% 2.18% 20.08%
Jericho_Highwayman 28 10 18 35.71% 1.39% 17.97%
Churchill_Sentai 36 10 26 27.78% 1.79% 13.15%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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