SHARE
TWEET

Untitled

a guest Sep 15th, 2019 102 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. rockset> WITH sum_days as (
  2.            select sum(EXTRACT(day from PARSE_DATETIME_ISO8601(o."DATETIMECLOSED") -
  3.                       PARSE_DATETIME_ISO8601(o."DATETIMEINIT"))) as days,
  4.                       o."REQCATEGORY"
  5.            from "oakland-call-center" o
  6.            where o."DATETIMECLOSED" != '' and o."DATETIMEINIT" != ''
  7.            GROUP BY o."SOURCE"
  8.          ),
  9.          sum_sources as (
  10.            select o."REQCATEGORY", count(*) as count from "oakland-call-center" o          
  11.            group by o.REQCATEGORY
  12.          )
  13.          select sum_days.REQCATEGORY, (sum_days.days / sum_sources.count) as avg_days
  14.          from sum_days
  15.          join sum_sources on sum_days.REQCATEGORY = sum_sources.REQCATEGORY
  16.  
  17. +----------------+------------+
  18. | REQCATEGORY    | avg_days   |
  19. |----------------+------------|
  20. | WATERSHED      | 260        |
  21. | METER_REPAIR   | 223        |
  22. | VEGCONTR       | 150        |
  23. | STREETSW       | 105        |
  24. | ROW            | 99         |
  25. | LAB            | 89         |
  26. | GIS            | 81         |
  27. | TREES          | 79         |
  28. | BLDGMAINT      | 63         |
  29. | PARKS          | 54         |
  30. | SURVEY         | 52         |
  31. | TRAFFIC_ENGIN  | 46         |
  32. ...................
  33. ...................
  34. | DRAINAGE       | 13         |
  35. | POLICE         | 11         |
  36. | ELECTRICAL     | 10         |
  37. | ROW_INSPECTORS | 8          |
  38. | RECYCLING      | 8          |
  39. | GRAFFITI       | 8          |
  40. | ILLDUMP        | 3          |
  41. | HE_CLEAN       | 2          |
  42. | OTHER          | 1          |
  43. | PARKING        | 0          |
  44. +----------------+------------+
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