daily pastebin goal
5%
SHARE
TWEET

School Tops Bad

genegreen Jul 15th, 2015 (edited) 1,273 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top