basalt_columns_heroes (view)

24 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Adora 1555 987 568 63.47% 8.93% 61.08%
PatFusty_Snowman 382 248 134 64.92% 2.19% 60.14%
Quincy_Cyber 2081 1160 921 55.74% 11.95% 53.61%
Quincy 1910 1021 889 53.46% 10.97% 51.22%
Adora_Fateweaver 245 133 112 54.29% 1.41% 48.05%
Benjamin 661 342 319 51.74% 3.80% 47.93%
Etienne 3480 1674 1806 48.10% 19.99% 46.44%
Etienne_Bee 1218 597 621 49.01% 7.00% 46.21%
Jericho 1222 584 638 47.79% 7.02% 44.99%
Ezili_SmudgeCat 104 56 48 53.85% 0.60% 44.26%
Bonnie 1347 612 735 45.43% 7.74% 42.78%
Jericho_StarCaptain 298 144 154 48.32% 1.71% 42.65%
Obyn 425 195 230 45.88% 2.44% 41.14%
Jericho_Highwayman 286 131 155 45.80% 1.64% 40.03%
PatFusty 128 61 67 47.66% 0.74% 39.00%
StrikerJones 95 45 50 47.37% 0.55% 37.33%
Benjamin_DJ 141 62 79 43.97% 0.81% 35.78%
StrikerJones_Biker 181 76 105 41.99% 1.04% 34.80%
Ezili 165 65 100 39.39% 0.95% 31.94%
Gwendolin_Science 373 135 238 36.19% 2.14% 31.32%
Churchill_Sentai 327 119 208 36.39% 1.88% 31.18%
Churchill 378 131 247 34.66% 2.17% 29.86%
Gwendolin 281 92 189 32.74% 1.61% 27.25%
Obyn_Ocean 129 36 93 27.91% 0.74% 20.17%

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;