up_on_the_roof_towers (view)

22 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Tower Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
MonkeyVillage 719 406 313 56.47% 11.21% 52.84%
IceMonkey 206 119 87 57.77% 3.21% 51.02%
DartMonkey 126 73 53 57.94% 1.96% 49.32%
SpikeFactory 391 207 184 52.94% 6.10% 47.99%
HeliPilot 139 78 61 56.12% 2.17% 47.87%
GlueGunner 340 176 164 51.76% 5.30% 46.45%
NinjaMonkey 407 208 199 51.11% 6.35% 46.25%
EngineerMonkey 146 79 67 54.11% 2.28% 46.03%
BoomerangMonkey 228 116 112 50.88% 3.55% 44.39%
SuperMonkey 456 223 233 48.90% 7.11% 44.32%
BombShooter 198 101 97 51.01% 3.09% 44.05%
SniperMonkey 567 270 297 47.62% 8.84% 43.51%
WizardMonkey 532 251 281 47.18% 8.29% 42.94%
TackShooter 461 218 243 47.29% 7.19% 42.73%
BananaFarm 607 278 329 45.80% 9.46% 41.84%
DartlingGunner 308 140 168 45.45% 4.80% 39.89%
MortarMonkey 44 24 20 54.55% 0.69% 39.83%
MonkeyAce 77 38 39 49.35% 1.20% 38.18%
Alchemist 215 96 119 44.65% 3.35% 38.01%
MonkeySub 95 41 54 43.16% 1.48% 33.20%
Druid 117 48 69 41.03% 1.82% 32.11%
MonkeyBuccaneer 35 17 18 48.57% 0.55% 32.01%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

CREATE VIEW up_on_the_roof_towers AS 
WITH up_on_the_roof AS
    (SELECT *
    FROM matches
    WHERE map = 'up_on_the_roof')
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 up_on_the_roof) * 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 up_on_the_roof
                  UNION ALL
                  SELECT lt2           AS Tower,
                         playerLeftWin AS Win
                  FROM up_on_the_roof
                  UNION ALL
                  SELECT lt3           AS Tower,
                         playerLeftWin AS Win
                  FROM up_on_the_roof
                  UNION ALL
                  SELECT rt1               AS Tower,
                         NOT playerLeftWin AS Win
                  FROM up_on_the_roof
                  UNION ALL
                  SELECT rt2               AS Tower,
                         NOT playerLeftWin AS Win
                  FROM up_on_the_roof
                  UNION ALL
                  SELECT rt3               AS Tower,
                         NOT playerLeftWin AS Win
                  FROM up_on_the_roof)
            GROUP BY Tower))
WHERE Wins >= 10 AND Losses >= 10 AND LENGTH(Winrate_LowerBound_95CI) = 6
ORDER BY Winrate_LowerBound_95CI DESC;