Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 |
- +----------------+------------+
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement