up_on_the_roof_towers (view)

22 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Wins

Tower Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
MonkeyVillage 735 411 324 55.92% 12.21% 52.33%
MonkeySub 94 56 38 59.57% 1.56% 49.65%
SuperMonkey 397 208 189 52.39% 6.60% 47.48%
IceMonkey 172 93 79 54.07% 2.86% 46.62%
TackShooter 451 230 221 51.00% 7.49% 46.38%
GlueGunner 311 160 151 51.45% 5.17% 45.89%
DartMonkey 121 66 55 54.55% 2.01% 45.67%
MonkeyAce 78 44 34 56.41% 1.30% 45.41%
BombShooter 182 95 87 52.20% 3.02% 44.94%
BananaFarm 540 265 275 49.07% 8.97% 44.86%
EngineerMonkey 122 65 57 53.28% 2.03% 44.43%
HeliPilot 145 76 69 52.41% 2.41% 44.28%
SpikeFactory 426 207 219 48.59% 7.08% 43.85%
SniperMonkey 459 221 238 48.15% 7.63% 43.58%
BoomerangMonkey 241 118 123 48.96% 4.00% 42.65%
WizardMonkey 503 224 279 44.53% 8.36% 40.19%
DartlingGunner 271 124 147 45.76% 4.50% 39.82%
Alchemist 181 85 96 46.96% 3.01% 39.69%
MortarMonkey 68 35 33 51.47% 1.13% 39.59%
Druid 139 66 73 47.48% 2.31% 39.18%
NinjaMonkey 332 139 193 41.87% 5.52% 36.56%
MonkeyBuccaneer 50 21 29 42.00% 0.83% 28.32%

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;