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
IceMonkey 560 324 236 57.86% 3.24% 53.77%
MonkeyVillage 2483 1358 1125 54.69% 14.36% 52.73%
SuperMonkey 1929 1038 891 53.81% 11.16% 51.59%
HeliPilot 661 353 308 53.40% 3.82% 49.60%
DartlingGunner 980 510 470 52.04% 5.67% 48.91%
GlueGunner 1274 656 618 51.49% 7.37% 48.75%
DartMonkey 945 483 462 51.11% 5.46% 47.92%
TackShooter 960 476 484 49.58% 5.55% 46.42%
Alchemist 481 239 242 49.69% 2.78% 45.22%
BoomerangMonkey 448 220 228 49.11% 2.59% 44.48%
MortarMonkey 265 133 132 50.19% 1.53% 44.17%
SpikeFactory 861 408 453 47.39% 4.98% 44.05%
SniperMonkey 1250 583 667 46.64% 7.23% 43.87%
BombShooter 332 159 173 47.89% 1.92% 42.52%
BananaFarm 1164 522 642 44.85% 6.73% 41.99%
NinjaMonkey 760 345 415 45.39% 4.40% 41.86%
WizardMonkey 870 388 482 44.60% 5.03% 41.29%
Druid 358 156 202 43.58% 2.07% 38.44%
MonkeyAce 162 74 88 45.68% 0.94% 38.01%
EngineerMonkey 180 75 105 41.67% 1.04% 34.46%
MonkeySub 187 75 112 40.11% 1.08% 33.08%
MonkeyBuccaneer 182 71 111 39.01% 1.05% 31.92%

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;