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
SpikeFactory 761 418 343 54.93% 10.16% 51.39%
BananaFarm 1585 852 733 53.75% 21.17% 51.30%
DartMonkey 481 267 214 55.51% 6.42% 51.07%
MortarMonkey 183 106 77 57.92% 2.44% 50.77%
DartlingGunner 499 271 228 54.31% 6.66% 49.94%
MonkeyAce 468 245 223 52.35% 6.25% 47.83%
Alchemist 396 205 191 51.77% 5.29% 46.85%
MonkeySub 709 353 356 49.79% 9.47% 46.11%
BoomerangMonkey 249 127 122 51.00% 3.33% 44.79%
SniperMonkey 364 170 194 46.70% 4.86% 41.58%
SuperMonkey 119 58 61 48.74% 1.59% 39.76%
GlueGunner 125 59 66 47.20% 1.67% 38.45%
Druid 133 62 71 46.62% 1.78% 38.14%
MonkeyBuccaneer 250 106 144 42.40% 3.34% 36.27%
WizardMonkey 307 127 180 41.37% 4.10% 35.86%
MonkeyVillage 125 54 71 43.20% 1.67% 34.52%
IceMonkey 143 58 85 40.56% 1.91% 32.51%
NinjaMonkey 195 76 119 38.97% 2.60% 32.13%
EngineerMonkey 153 54 99 35.29% 2.04% 27.72%
HeliPilot 75 28 47 37.33% 1.00% 26.39%
TackShooter 126 34 92 26.98% 1.68% 19.23%
BombShooter 42 14 28 33.33% 0.56% 19.08%

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;