Oracle: JIRA issue activity breakdown

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

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?