pirate_cove_heroes (view)

18 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Wins, Losses

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
StrikerJones 68 52 16 76.47% 2.37% 66.39%
Adora 174 109 65 62.64% 6.05% 55.46%
Adora_Fateweaver 92 56 36 60.87% 3.20% 50.90%
PatFusty_Snowman 157 91 66 57.96% 5.46% 50.24%
Etienne_Bee 245 137 108 55.92% 8.52% 49.70%
Quincy_Cyber 119 64 55 53.78% 4.14% 44.82%
Ezili 141 74 67 52.48% 4.91% 44.24%
Etienne 625 300 325 48.00% 21.75% 44.08%
Jericho 243 120 123 49.38% 8.46% 43.10%
Jericho_StarCaptain 149 74 75 49.66% 5.18% 41.64%
Quincy 248 113 135 45.56% 8.63% 39.37%
Bonnie 151 69 82 45.70% 5.25% 37.75%
Obyn 34 17 17 50.00% 1.18% 33.19%
Jericho_Highwayman 48 22 26 45.83% 1.67% 31.74%
Gwendolin 67 27 40 40.30% 2.33% 28.55%
Gwendolin_Science 107 40 67 37.38% 3.72% 28.22%
Churchill 37 13 24 35.14% 1.29% 19.75%
Benjamin 45 13 32 28.89% 1.57% 15.65%

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;