genegreen

School Tops Bad

Jul 15th, 2015
2,115
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select host(subnet) as subnet, type, resources.domain, traffic / 2 ^ 20
  2. from (select subnet, type, resource_id, traffic, rank() OVER (PARTITION BY subnet, type ORDER BY traffic DESC ) as rank from
  3. (
  4. select std.subnet as subnet,
  5. 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,
  6. std.resource_id as resource_id, sum(std.traffic) as traffic from statistics_trimmed_domains std
  7. inner join resource_categories rc using(resource_id) inner join view_categories wc using(category_id)
  8. 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))
  9. group by 1, 2, 3
  10. having sum(std.traffic) / 2^20 > 5
  11. ) as foo ) as foo
  12. left join resources using(resource_id)
  13. where rank <= 10
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×