basalt_columns_towers (view)

17 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Wins, Pickrate

Tower Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
BananaFarm 338 185 153 54.73% 22.44% 49.43%
MonkeyAce 106 58 48 54.72% 7.04% 45.24%
WizardMonkey 79 44 35 55.70% 5.25% 44.74%
DartlingGunner 178 90 88 50.56% 11.82% 43.22%
Alchemist 131 67 64 51.15% 8.70% 42.58%
DartMonkey 63 34 29 53.97% 4.18% 41.66%
EngineerMonkey 45 25 20 55.56% 2.99% 41.04%
MortarMonkey 29 17 12 58.62% 1.93% 40.70%
SniperMonkey 57 30 27 52.63% 3.78% 39.67%
IceMonkey 32 18 14 56.25% 2.12% 39.06%
SpikeFactory 107 49 58 45.79% 7.10% 36.35%
GlueGunner 29 14 15 48.28% 1.93% 30.09%
BoomerangMonkey 52 22 30 42.31% 3.45% 28.88%
MonkeyBuccaneer 44 19 25 43.18% 2.92% 28.55%
SuperMonkey 34 15 19 44.12% 2.26% 27.43%
NinjaMonkey 41 17 24 41.46% 2.72% 26.38%
MonkeySub 55 18 37 32.73% 3.65% 20.33%

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;