basalt_columns_towers (view)

22 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Tower Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
MortarMonkey 90 62 28 68.89% 1.42% 59.32%
BananaFarm 1336 715 621 53.52% 21.13% 50.84%
MonkeySub 655 352 303 53.74% 10.36% 49.92%
DartMonkey 230 127 103 55.22% 3.64% 48.79%
MonkeyAce 393 208 185 52.93% 6.21% 47.99%
SpikeFactory 550 286 264 52.00% 8.70% 47.82%
DartlingGunner 513 265 248 51.66% 8.11% 47.33%
BoomerangMonkey 227 118 109 51.98% 3.59% 45.48%
NinjaMonkey 190 99 91 52.11% 3.00% 45.00%
WizardMonkey 294 142 152 48.30% 4.65% 42.59%
Alchemist 293 140 153 47.78% 4.63% 42.06%
SniperMonkey 357 160 197 44.82% 5.65% 39.66%
EngineerMonkey 132 60 72 45.45% 2.09% 36.96%
IceMonkey 161 71 90 44.10% 2.55% 36.43%
SuperMonkey 91 41 50 45.05% 1.44% 34.83%
MonkeyBuccaneer 176 72 104 40.91% 2.78% 33.65%
MonkeyVillage 137 56 81 40.88% 2.17% 32.64%
Druid 88 36 52 40.91% 1.39% 30.64%
TackShooter 155 58 97 37.42% 2.45% 29.80%
HeliPilot 101 39 62 38.61% 1.60% 29.12%
BombShooter 41 17 24 41.46% 0.65% 26.38%
GlueGunner 114 38 76 33.33% 1.80% 24.68%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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