sands_of_time_towers (view)

22 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Tower Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
MortarMonkey 229 140 89 61.14% 1.13% 54.82%
IceMonkey 832 474 358 56.97% 4.09% 53.61%
BananaFarm 2542 1392 1150 54.76% 12.50% 52.83%
MonkeyAce 261 150 111 57.47% 1.28% 51.47%
EngineerMonkey 477 265 212 55.56% 2.35% 51.10%
MonkeySub 905 491 414 54.25% 4.45% 51.01%
DartMonkey 826 439 387 53.15% 4.06% 49.74%
BoomerangMonkey 660 345 315 52.27% 3.24% 48.46%
Druid 918 468 450 50.98% 4.51% 47.75%
DartlingGunner 1018 504 514 49.51% 5.00% 46.44%
SuperMonkey 1013 496 517 48.96% 4.98% 45.89%
MonkeyBuccaneer 639 316 323 49.45% 3.14% 45.58%
NinjaMonkey 1027 498 529 48.49% 5.05% 45.43%
SpikeFactory 1056 510 546 48.30% 5.19% 45.28%
WizardMonkey 1113 533 580 47.89% 5.47% 44.95%
GlueGunner 1329 629 700 47.33% 6.53% 44.64%
Alchemist 729 350 379 48.01% 3.58% 44.38%
MonkeyVillage 1633 763 870 46.72% 8.03% 44.30%
HeliPilot 514 248 266 48.25% 2.53% 43.93%
BombShooter 584 272 312 46.58% 2.87% 42.53%
TackShooter 883 394 489 44.62% 4.34% 41.34%
SniperMonkey 1152 493 659 42.80% 5.66% 39.94%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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