sands_of_time_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
PatFusty_Snowman 100 68 32 68.00% 6.73% 58.86%
Jericho 96 61 35 63.54% 6.46% 53.91%
Quincy_Cyber 178 97 81 54.49% 11.98% 47.18%
Bonnie 111 59 52 53.15% 7.47% 43.87%
Obyn 136 70 66 51.47% 9.15% 43.07%
Etienne 119 60 59 50.42% 8.01% 41.44%
Ezili_SmudgeCat 25 14 11 56.00% 1.68% 36.54%
Gwendolin 41 21 20 51.22% 2.76% 35.92%
Adora 60 29 31 48.33% 4.04% 35.69%
Quincy 77 36 41 46.75% 5.18% 35.61%
Etienne_Bee 95 41 54 43.16% 6.39% 33.20%
Benjamin 148 59 89 39.86% 9.96% 31.98%
Churchill 31 15 16 48.39% 2.09% 30.79%
Churchill_Sentai 56 23 33 41.07% 3.77% 28.19%
Obyn_Ocean 34 15 19 44.12% 2.29% 27.43%
Ezili 46 19 27 41.30% 3.10% 27.08%
PatFusty 33 14 19 42.42% 2.22% 25.56%
Gwendolin_Science 37 10 27 27.03% 2.49% 12.72%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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