-- ## 33: Popular Tickets - Done ## --

-- In order to balance report {32} (tickets that are on the TODO list for a long time), we can also show that we managed to implement popular feature requests over time ;-)
-- 
-- The criterions for inclusion for the feature request or bug report is that '''at least 10 different people participated to the ticket activity'''.
-- 
-- Tickets are ordered by ''popularity'', defined as the amount of activity on a ticket over time.

-- Note: this is for a PostgreSQL database, things like:
--
--         (SELECT extract(epoch from current_timestamp) as now) time
--
--       probably won't work with other databases.

SELECT p.value AS __color__,
  (SELECT
    ROUND(( (tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(COALESCE(t.cc, '')) * 1.5) 
             / (delta/86400.0)
          ) * 10.0
         ) / 10.0
   FROM (SELECT ((time.now * 1000000 - changetime) / 1000000.0) AS delta
        ) AS ct
  ) AS Popularity,
 -- 
 --  ROUND(((tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(COALESCE(t.cc, '')) * 1.5) 
 -- / ((time.now - t.time)/(86400.0 * 30))) * 10.0) / 10.0 AS Popularity,
 --
  t.type,
  id AS ticket, summary, component, 
  milestone, version,
  time as created
FROM ticket t LEFT JOIN
   (SELECT ticket, count(distinct author) as author_cnt,
          count(*) as change_cnt
    FROM ticket_change
    WHERE field = 'comment' GROUP BY ticket) tc ON (t.id = tc.ticket),
   enum p,
   (SELECT extract(epoch from current_timestamp) as now) time
WHERE t.status = 'closed' AND t.resolution = 'fixed'
  -- AND time.now - t.time > 86400.0 * 30 * 2
  -- well, the above was not really needed as they are all older than 1 year...
  AND tc.author_cnt >= 10
  AND p.name = t.priority AND p.type = 'priority'
ORDER BY
 ( (tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(COALESCE(t.cc, '')) * 1.5) 
    / ((time.now * 1000000 - changetime) / 1000000.0) ) desc,  
 p.value, t.severity, time