pirate_cove_heroes (view)

18 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
Jericho_Highwayman 206 123 83 59.71% 6.75% 53.01%
Quincy_Cyber 638 362 276 56.74% 20.90% 52.90%
Etienne_Bee 396 224 172 56.57% 12.98% 51.68%
StrikerJones 51 32 19 62.75% 1.67% 49.48%
Adora 51 30 21 58.82% 1.67% 45.32%
PatFusty_Snowman 90 50 40 55.56% 2.95% 45.29%
Ezili 109 58 51 53.21% 3.57% 43.84%
Jericho 315 151 164 47.94% 10.32% 42.42%
Quincy 470 219 251 46.60% 15.40% 42.09%
Jericho_StarCaptain 60 32 28 53.33% 1.97% 40.71%
Gwendolin 58 24 34 41.38% 1.90% 28.70%
Churchill_Sentai 54 22 32 40.74% 1.77% 27.64%
Etienne 212 72 140 33.96% 6.95% 27.59%
Gwendolin_Science 41 17 24 41.46% 1.34% 26.38%
Bonnie 99 33 66 33.33% 3.24% 24.05%
Obyn 42 16 26 38.10% 1.38% 23.41%
PatFusty 23 10 13 43.48% 0.75% 23.22%
Churchill 38 12 26 31.58% 1.25% 16.80%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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