Query that sums up worklog entries for a project for the past 7 days. It then ranks and outputs the top 3 projects.

worklogs.sql

with cte as 
  ( 
    select
      ss.pname
      ,ss.time_worked
      ,rank() over (order by ss.time_worked desc) project_rank
    from
    (
      select
        p.pname
        ,sum(w.timeworked) as time_worked
      from 
        jiraissue ji
      join project p
        on ji.project = p.id
      join worklog w
        on ji.id = w.issueid
      where w.created >= current_date - interval '7 day'
      group by 1
    ) ss
  ) 
select * from cte
where project_rank <=3;

Example Output:

pname     | time_worked | project_rank
---------------+-------------+--------------
 Project Delta |       83700 |            1
 Project Alpha |       37920 |            2
 Project Beta  |       30900 |            3
(3 rows)