Oracle: JIRA issue activity breakdown

So you need how many issues get created, changed or commented per hour, each day.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

SELECT * FROM (    SELECT * FROM (       SELECT          ID                             AS "ISSUEID",          TO_CHAR(CREATED, 'YYYY-MM-DD') AS CREATED  ,          TO_CHAR(CREATED, 'HH24')       AS HOURS    ,          TO_CHAR(CREATED, 'DAY')        AS DOW      ,          'NEW'                          AS "TYPE"       FROM JIRAISSUE       WHERE CREATED >= TO_DATE('2015-01-01', 'YYYY-MM-DD')       UNION ALL       SELECT          ISSUEID                                  ,          TO_CHAR(CREATED, 'YYYY-MM-DD') AS CREATED,          TO_CHAR(CREATED, 'HH24')       AS HOURS  ,          TO_CHAR(CREATED, 'DAY')        AS DOW    ,          'CHANGE'                       AS "TYPE"       FROM CHANGEGROUP       WHERE CREATED >= TO_DATE('2015-01-01', 'YYYY-MM-DD')       UNION ALL       SELECT          ISSUEID                                  ,          TO_CHAR(CREATED, 'YYYY-MM-DD') AS CREATED,          TO_CHAR(CREATED, 'HH24')       AS HOURS  ,          TO_CHAR(CREATED, 'DAY')        AS DOW    ,          'COMMENT'                      AS "TYPE"       FROM JIRAACTION       WHERE CREATED >= TO_DATE('2015-01-01', 'YYYY-MM-DD')    )    PIVOT (       COUNT(ISSUEID) FOR (HOURS)       IN ('00', '01', '02', '03', '04', '05', '06', '07',          '08', '09', '10', '11', '12', '13', '14', '15',          '16', '17', '18', '19', '20', '21', '22', '23')    ) ) ORDER BY CREATED DESC, "TYPE" DESC;

Not my best, but what do you expect in ten minutes?