days_to_resolve.sql

rockset> WITH sum_days as (
           select sum(EXTRACT(day from PARSE_DATETIME_ISO8601(o."DATETIMECLOSED") -
                      PARSE_DATETIME_ISO8601(o."DATETIMEINIT"))) as days, 
                      o."REQCATEGORY"
           from "oakland-call-center" o
           where o."DATETIMECLOSED" != '' and o."DATETIMEINIT" != ''
           GROUP BY o."SOURCE"
         ),
         sum_sources as (
           select o."REQCATEGORY", count(*) as count from "oakland-call-center" o           
           group by o.REQCATEGORY
         )
         select sum_days.REQCATEGORY, (sum_days.days / sum_sources.count) as avg_days
         from sum_days
         join sum_sources on sum_days.REQCATEGORY = sum_sources.REQCATEGORY

+----------------+------------+
| REQCATEGORY    | avg_days   |
|----------------+------------|
| WATERSHED      | 260        |
| METER_REPAIR   | 223        |
| VEGCONTR       | 150        |
| STREETSW       | 105        |
| ROW            | 99         |
| LAB            | 89         |
| GIS            | 81         |
| TREES          | 79         |
| BLDGMAINT      | 63         |
| PARKS          | 54         |
| SURVEY         | 52         |
| TRAFFIC_ENGIN  | 46         |
...................
...................
| DRAINAGE       | 13         |
| POLICE         | 11         |
| ELECTRICAL     | 10         |
| ROW_INSPECTORS | 8          |
| RECYCLING      | 8          |
| GRAFFITI       | 8          |
| ILLDUMP        | 3          |
| HE_CLEAN       | 2          |
| OTHER          | 1          |
| PARKING        | 0          |
+----------------+------------+