building_site_scene_heroes (view)

16 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Losses, Pickrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
StrikerJones 53 39 14 73.58% 2.76% 61.72%
Adora_Fateweaver 107 74 33 69.16% 5.58% 60.41%
Adora 355 219 136 61.69% 18.51% 56.63%
PatFusty_Snowman 38 24 14 63.16% 1.98% 47.82%
Etienne_Bee 101 56 45 55.45% 5.27% 45.75%
Etienne 465 206 259 44.30% 24.24% 39.79%
Ezili 51 27 24 52.94% 2.66% 39.24%
Quincy_Cyber 92 44 48 47.83% 4.80% 37.62%
Quincy 184 79 105 42.93% 9.59% 35.78%
Bonnie 79 32 47 40.51% 4.12% 29.68%
Jericho 92 36 56 39.13% 4.80% 29.16%
Gwendolin 38 17 21 44.74% 1.98% 28.93%
Gwendolin_Science 63 25 38 39.68% 3.28% 27.60%
Jericho_Highwayman 29 11 18 37.93% 1.51% 20.27%
Jericho_StarCaptain 44 15 29 34.09% 2.29% 20.08%
Obyn 28 10 18 35.71% 1.46% 17.97%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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