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
Quincy_Cyber 1331 752 579 56.50% 39.40% 53.84%
StrikerJones 88 53 35 60.23% 2.61% 50.00%
Jericho_Highwayman 208 112 96 53.85% 6.16% 47.07%
Etienne_Bee 204 106 98 51.96% 6.04% 45.10%
Quincy 349 174 175 49.86% 10.33% 44.61%
Adora 78 43 35 55.13% 2.31% 44.09%
StrikerJones_Biker 65 33 32 50.77% 1.92% 38.62%
PatFusty_Snowman 50 26 24 52.00% 1.48% 38.15%
Jericho 217 94 123 43.32% 6.42% 36.72%
Ezili 71 34 37 47.89% 2.10% 36.27%
Jericho_StarCaptain 49 24 25 48.98% 1.45% 34.98%
Adora_Fateweaver 34 17 17 50.00% 1.01% 33.19%
Etienne 254 94 160 37.01% 7.52% 31.07%
Bonnie 79 33 46 41.77% 2.34% 30.90%
Churchill 54 21 33 38.89% 1.60% 25.89%
Churchill_Sentai 45 16 29 35.56% 1.33% 21.57%
Gwendolin 48 15 33 31.25% 1.42% 18.14%
PatFusty 44 14 30 31.82% 1.30% 18.06%

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;