castle_ruins_heroes (view)

24 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
Quincy_Cyber 2294 1324 970 57.72% 6.79% 55.69%
Jericho 2592 1459 1133 56.29% 7.67% 54.38%
PatFusty_Snowman 927 522 405 56.31% 2.74% 53.12%
Jericho_StarCaptain 619 346 273 55.90% 1.83% 51.99%
Quincy 2689 1424 1265 52.96% 7.96% 51.07%
Adora_Fateweaver 377 210 167 55.70% 1.12% 50.69%
Etienne_Bee 3029 1578 1451 52.10% 8.97% 50.32%
Obyn 2971 1544 1427 51.97% 8.80% 50.17%
Benjamin 1448 741 707 51.17% 4.29% 48.60%
Ezili 848 433 415 51.06% 2.51% 47.70%
Adora 955 478 477 50.05% 2.83% 46.88%
Ezili_SmudgeCat 304 158 146 51.97% 0.90% 46.36%
PatFusty 379 191 188 50.40% 1.12% 45.36%
Gwendolin_Science 1992 947 1045 47.54% 5.90% 45.35%
StrikerJones 467 232 235 49.68% 1.38% 45.14%
Jericho_Highwayman 601 295 306 49.08% 1.78% 45.09%
Etienne 5393 2500 2893 46.36% 15.97% 45.03%
StrikerJones_Biker 466 229 237 49.14% 1.38% 44.60%
Benjamin_DJ 263 127 136 48.29% 0.78% 42.25%
Bonnie 2248 988 1260 43.95% 6.65% 41.90%
Gwendolin 1065 432 633 40.56% 3.15% 37.61%
Obyn_Ocean 635 253 382 39.84% 1.88% 36.03%
Churchill_Sentai 631 251 380 39.78% 1.87% 35.96%
Churchill 587 228 359 38.84% 1.74% 34.90%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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