genegreen

School Tops Better

Jul 15th, 2015
2,108
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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

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.

×