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
BananaFarm 2554 1412 1142 55.29% 19.68% 53.36%
DartlingGunner 678 376 302 55.46% 5.22% 51.72%
EngineerMonkey 702 385 317 54.84% 5.41% 51.16%
MonkeyAce 515 284 231 55.15% 3.97% 50.85%
WizardMonkey 1348 709 639 52.60% 10.39% 49.93%
BoomerangMonkey 536 286 250 53.36% 4.13% 49.13%
TackShooter 1245 636 609 51.08% 9.59% 48.31%
MortarMonkey 167 92 75 55.09% 1.29% 47.55%
MonkeySub 388 196 192 50.52% 2.99% 45.54%
NinjaMonkey 601 275 326 45.76% 4.63% 41.77%
SuperMonkey 444 206 238 46.40% 3.42% 41.76%
SniperMonkey 681 307 374 45.08% 5.25% 41.34%
IceMonkey 355 163 192 45.92% 2.74% 40.73%
MonkeyVillage 648 282 366 43.52% 4.99% 39.70%
Druid 422 183 239 43.36% 3.25% 38.64%
HeliPilot 294 123 171 41.84% 2.27% 36.20%
BombShooter 162 70 92 43.21% 1.25% 35.58%
SpikeFactory 353 143 210 40.51% 2.72% 35.39%
GlueGunner 343 138 205 40.23% 2.64% 35.04%
Alchemist 184 77 107 41.85% 1.42% 34.72%
DartMonkey 184 76 108 41.30% 1.42% 34.19%
MonkeyBuccaneer 174 70 104 40.23% 1.34% 32.94%

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;