School Tops Bad

select host(subnet) as subnet, type, resources.domain, traffic / 2 ^ 20
from (select subnet, type, resource_id, traffic, rank() OVER (PARTITION BY subnet, type ORDER BY traffic DESC ) as rank from
(
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
inner join resource_categories rc using(resource_id) inner join view_categories wc using(category_id)
where extract(year from std.day) = 2015 and extract(month from std.day) = 6 and not exists (select * from resource_categories rc where rc.resource_id = std.resource_id and rc.category_id in (1, 33))
group by 1, 2, 3
having sum(std.traffic) / 2^20 > 5
) as foo ) as foo
left join resources using(resource_id)
where rank <= 10