ports_towers (view)

11 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Games, Winrate, Pickrate

Tower Games Wins Losses Winrate Pickrate Winrate_LowerBound_95CI
MortarMonkey 68 46 22 67.65% 4.44% 56.53%
Alchemist 204 126 78 61.76% 13.33% 55.10%
MonkeyAce 304 182 122 59.87% 19.87% 54.36%
MonkeySub 253 134 119 52.96% 16.54% 46.81%
BananaFarm 253 127 126 50.20% 16.54% 44.04%
HeliPilot 50 21 29 42.00% 3.27% 28.32%
WizardMonkey 59 22 37 37.29% 3.86% 24.95%
MonkeyBuccaneer 40 16 24 40.00% 2.61% 24.82%
DartMonkey 35 14 21 40.00% 2.29% 23.77%
Druid 24 10 14 41.67% 1.57% 21.94%
SniperMonkey 47 15 32 31.91% 3.07% 18.59%

Advanced export

JSON shape: default, array, newline-delimited

CSV options: 

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