daily pastebin goal
5%
SHARE
TWEET

School Tops Better

genegreen Jul 15th, 2015 (edited) 1,276 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. WITH
  2. params ("start", "end") AS
  3. (
  4.     SELECT
  5.         CAST(DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 MONTH') AS DATE),
  6.         CAST(DATE_TRUNC('month', CURRENT_DATE - INTERVAL '0 MONTH') AS DATE)
  7. ),
  8. cteFiltered
  9. AS
  10. (
  11.     SELECT
  12.         std.subnet AS subnet,
  13.         CASE
  14.             WHEN root_category_id = 12 THEN 'edu'
  15.         ELSE CASE
  16.             WHEN root_category_id = 11 or root_category_id = 14 THEN 'info'
  17.         ELSE CASE
  18.             WHEN root_category_id = 813 THEN 'region'
  19.         ELSE
  20.             'other'
  21.             END END END AS "type",
  22.         std.resource_id AS resource_id,
  23.         sum(std.traffic) AS traffic
  24.     FROM
  25.         statistics_trimmed_domains std
  26.     JOIN
  27.         resource_categories rc USING(resource_id)
  28.     JOIN
  29.         view_categories wc USING(category_id)
  30.     JOIN
  31.         resources r USING(resource_id)
  32.     WHERE
  33.         std.day >= (SELECT "start" FROM params) AND
  34.         std.day < (SELECT "end" FROM params) AND
  35.         NOT EXISTS
  36.             (
  37.             SELECT
  38.                 *
  39.             FROM
  40.                 resource_categories rc
  41.             WHERE
  42.                 rc.resource_id = std.resource_id AND rc.category_id IN (1, 33)
  43.             )
  44.     GROUP BY
  45.         subnet,
  46.         "type",
  47.         resource_id
  48.     HAVING
  49.         SUM(std.traffic) / 2^20 > 5
  50. ),
  51. cteRanked
  52. AS
  53. (
  54.     SELECT
  55.         subnet,
  56.         "type",
  57.         r.domain AS "domain",
  58.         traffic,
  59.         RANK() OVER (PARTITION BY subnet, "type" ORDER BY traffic DESC ) AS rank
  60.     FROM
  61.         cteFiltered
  62.     JOIN
  63.         resources r USING(resource_id)
  64. )
  65.  
  66. SELECT
  67.     host(subnet) AS subnet,
  68.     "type",
  69.     "domain",
  70.     traffic / 2 ^ 20 AS trafficMb
  71. FROM
  72.     cteRanked
  73. WHERE
  74.     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