pirate_cove_heroes (view)

19 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Losses, Winrate

Hero Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
StrikerJones 68 47 21 69.12% 2.35% 58.14%
Adora 211 133 78 63.03% 7.28% 56.52%
PatFusty_Snowman 341 193 148 56.60% 11.77% 51.34%
Quincy_Cyber 157 86 71 54.78% 5.42% 46.99%
Jericho_StarCaptain 119 66 53 55.46% 4.11% 46.53%
Bonnie 143 77 66 53.85% 4.93% 45.68%
Quincy 295 149 146 50.51% 10.18% 44.80%
Etienne_Bee 196 99 97 50.51% 6.76% 43.51%
Jericho 263 127 136 48.29% 9.08% 42.25%
Adora_Fateweaver 71 38 33 53.52% 2.45% 41.92%
Ezili 101 52 49 51.49% 3.49% 41.74%
Etienne 498 212 286 42.57% 17.18% 38.23%
PatFusty 58 27 31 46.55% 2.00% 33.71%
Gwendolin 63 28 35 44.44% 2.17% 32.17%
Jericho_Highwayman 61 25 36 40.98% 2.10% 28.64%
Churchill_Sentai 37 16 21 43.24% 1.28% 27.28%
Gwendolin_Science 102 35 67 34.31% 3.52% 25.10%
Benjamin 34 14 20 41.18% 1.17% 24.63%
Obyn 27 10 17 37.04% 0.93% 18.82%

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;