School Tops Better

WITH
params ("start", "end") AS
(
    SELECT
        CAST(DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 MONTH') AS DATE),
        CAST(DATE_TRUNC('month', CURRENT_DATE - INTERVAL '0 MONTH') AS DATE)
),
cteFiltered
AS
(
    SELECT
        std.subnet AS subnet,
        CASE
            WHEN root_category_id = 12 THEN 'edu'
        ELSE CASE
            WHEN root_category_id = 11 or root_category_id = 14 THEN 'info'
        ELSE CASE
            WHEN root_category_id = 813 THEN 'region'
        ELSE
            'other'
            END END END AS "type",
        std.resource_id AS resource_id,
        sum(std.traffic) AS traffic
    FROM
        statistics_trimmed_domains std
    JOIN
        resource_categories rc USING(resource_id)
    JOIN
        view_categories wc USING(category_id)
    JOIN
        resources r USING(resource_id)
    WHERE
        std.day >= (SELECT "start" FROM params) AND
        std.day < (SELECT "end" FROM params) AND
        NOT EXISTS
            (
            SELECT
                *
            FROM
                resource_categories rc
            WHERE
                rc.resource_id = std.resource_id AND rc.category_id IN (1, 33)
            )
    GROUP BY
        subnet,
        "type",
        resource_id
    HAVING
        SUM(std.traffic) / 2^20 > 5
),
cteRanked
AS
(
    SELECT
        subnet,
        "type",
        r.domain AS "domain",
        traffic,
        RANK() OVER (PARTITION BY subnet, "type" ORDER BY traffic DESC ) AS rank
    FROM
        cteFiltered
    JOIN
        resources r USING(resource_id)
)

SELECT
    host(subnet) AS subnet,
    "type",
    "domain",
    traffic / 2 ^ 20 AS trafficMb
FROM
    cteRanked
WHERE
    rank <= 10