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 1272 801 471 62.97% 2.44% 60.32%
DartMonkey 3196 1803 1393 56.41% 6.12% 54.70%
BananaFarm 9541 5267 4274 55.20% 18.27% 54.21%
SpikeFactory 4450 2349 2101 52.79% 8.52% 51.32%
MonkeyAce 3700 1950 1750 52.70% 7.08% 51.09%
DartlingGunner 3986 2064 1922 51.78% 7.63% 50.23%
Alchemist 3455 1786 1669 51.69% 6.61% 50.03%
BoomerangMonkey 2060 1070 990 51.94% 3.94% 49.78%
IceMonkey 879 446 433 50.74% 1.68% 47.43%
MonkeySub 3039 1488 1551 48.96% 5.82% 47.19%
EngineerMonkey 1308 616 692 47.09% 2.50% 44.39%
SniperMonkey 2835 1261 1574 44.48% 5.43% 42.65%
MonkeyBuccaneer 2270 1014 1256 44.67% 4.35% 42.62%
SuperMonkey 1201 527 674 43.88% 2.30% 41.07%
WizardMonkey 2294 985 1309 42.94% 4.39% 40.91%
Druid 842 365 477 43.35% 1.61% 40.00%
NinjaMonkey 1279 533 746 41.67% 2.45% 38.97%
GlueGunner 1428 570 858 39.92% 2.73% 37.38%
MonkeyVillage 1138 453 685 39.81% 2.18% 36.96%
HeliPilot 678 269 409 39.68% 1.30% 35.99%
TackShooter 983 355 628 36.11% 1.88% 33.11%
BombShooter 402 146 256 36.32% 0.77% 31.62%

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;