all_maps (view)

26 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: Average Duration, Average End Round, Seconds per Round

CREATE VIEW all_maps AS 
WITH elite_maps AS (
    SELECT map, duration, endRound
    FROM matches
    WHERE map NOT IN ('bloontonium_mines', 'docks', 'in_the_wall', 'island_base', 'thin_ice')
)
SELECT map AS Map,
       Games,
       printf('%.2f%%', Games * 100.0 / (SELECT COUNT(*) FROM elite_maps)) AS Pickrate,
       REPLACE((CASE
                    WHEN CAST(ROUND(avg_duration / 60, 0) AS INTEGER) < 10
                        THEN '0' || CAST(ROUND(avg_duration / 60, 0) AS TEXT)
                    ELSE CAST(ROUND(avg_duration / 60, 0) AS TEXT)
           END) || ':' ||
               (CASE
                    WHEN CAST(ROUND(avg_duration % 60, 0) AS INTEGER) < 10
                        THEN '0' || CAST(ROUND(avg_duration % 60, 0) AS TEXT)
                    ELSE CAST(ROUND(avg_duration % 60, 0) AS TEXT)
                   END), '.0', '')                                 AS [Average Duration],
       ROUND(avg_end_round, 2)                                     AS [Average End Round],
       ROUND(avg_duration / avg_end_round, 2)                      AS [Seconds per Round]
FROM (SELECT map,
             AVG(duration) AS avg_duration,
             AVG(endRound) AS avg_end_round,
             COUNT(*)      AS Games
      FROM elite_maps
      GROUP BY map);