castle_ruins_towers (view)

22 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Tower Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
MonkeyAce 402 248 154 61.69% 2.89% 56.94%
EngineerMonkey 504 298 206 59.13% 3.62% 54.84%
BananaFarm 2133 1160 973 54.38% 15.32% 52.27%
BoomerangMonkey 560 314 246 56.07% 4.02% 51.96%
MortarMonkey 285 163 122 57.19% 2.05% 51.45%
DartMonkey 606 317 289 52.31% 4.35% 48.33%
WizardMonkey 856 438 418 51.17% 6.15% 47.82%
MonkeyVillage 857 433 424 50.53% 6.16% 47.18%
DartlingGunner 689 345 344 50.07% 4.95% 46.34%
SuperMonkey 685 329 356 48.03% 4.92% 44.29%
MonkeySub 534 259 275 48.50% 3.84% 44.26%
HeliPilot 352 174 178 49.43% 2.53% 44.21%
Druid 576 277 299 48.09% 4.14% 44.01%
GlueGunner 899 423 476 47.05% 6.46% 43.79%
TackShooter 809 382 427 47.22% 5.81% 43.78%
NinjaMonkey 645 293 352 45.43% 4.63% 41.58%
MonkeyBuccaneer 333 151 182 45.35% 2.39% 40.00%
SniperMonkey 824 357 467 43.33% 5.92% 39.94%
BombShooter 378 168 210 44.44% 2.72% 39.44%
Alchemist 394 172 222 43.65% 2.83% 38.76%
IceMonkey 274 121 153 44.16% 1.97% 38.28%
SpikeFactory 325 138 187 42.46% 2.33% 37.09%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

CREATE VIEW castle_ruins_towers AS 
WITH castle_ruins AS
    (SELECT *
    FROM matches
    WHERE map = 'castle_ruins')
SELECT Tower,
       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 Tower,
             Games,
             Wins,
             Losses,
             CAST(Wins AS REAL) / Games AS WR,
             CAST(Games AS REAL) / (
                 (SELECT COUNT(*)
                  FROM castle_ruins) * 6)    AS PR
      FROM (SELECT Tower,
                   SUM(Win) + SUM(NOT Win) AS Games,
                   SUM(Win)                AS Wins,
                   SUM(NOT Win)            AS Losses
            FROM (SELECT lt1           AS Tower,
                         playerLeftWin AS Win
                  FROM castle_ruins
                  UNION ALL
                  SELECT lt2           AS Tower,
                         playerLeftWin AS Win
                  FROM castle_ruins
                  UNION ALL
                  SELECT lt3           AS Tower,
                         playerLeftWin AS Win
                  FROM castle_ruins
                  UNION ALL
                  SELECT rt1               AS Tower,
                         NOT playerLeftWin AS Win
                  FROM castle_ruins
                  UNION ALL
                  SELECT rt2               AS Tower,
                         NOT playerLeftWin AS Win
                  FROM castle_ruins
                  UNION ALL
                  SELECT rt3               AS Tower,
                         NOT playerLeftWin AS Win
                  FROM castle_ruins)
            GROUP BY Tower))
WHERE Wins >= 10 AND Losses >= 10 AND LENGTH(Winrate_LowerBound_95CI) = 6
ORDER BY Winrate_LowerBound_95CI DESC;