up_on_the_roof_towers (view)

22 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games

Tower Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
IceMonkey 471 282 189 59.87% 4.04% 55.45%
MonkeyVillage 1527 844 683 55.27% 13.10% 52.78%
HeliPilot 544 293 251 53.86% 4.67% 49.67%
GlueGunner 782 415 367 53.07% 6.71% 49.57%
DartMonkey 394 211 183 53.55% 3.38% 48.63%
MortarMonkey 154 86 68 55.84% 1.32% 48.00%
TackShooter 715 369 346 51.61% 6.13% 47.95%
DartlingGunner 629 326 303 51.83% 5.40% 47.92%
SuperMonkey 1156 578 578 50.00% 9.92% 47.12%
Alchemist 322 167 155 51.86% 2.76% 46.41%
SpikeFactory 715 356 359 49.79% 6.13% 46.13%
BoomerangMonkey 376 192 184 51.06% 3.23% 46.01%
BananaFarm 935 444 491 47.49% 8.02% 44.29%
NinjaMonkey 532 247 285 46.43% 4.56% 42.19%
SniperMonkey 647 274 373 42.35% 5.55% 38.54%
MonkeyAce 104 50 54 48.08% 0.89% 38.47%
WizardMonkey 750 315 435 42.00% 6.43% 38.47%
Druid 247 107 140 43.32% 2.12% 37.14%
BombShooter 227 98 129 43.17% 1.95% 36.73%
EngineerMonkey 226 97 129 42.92% 1.94% 36.47%
MonkeySub 138 57 81 41.30% 1.18% 33.09%
MonkeyBuccaneer 67 21 46 31.34% 0.57% 20.24%

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;