Getting the streak of tasks completed

Question

I have a table called task activity. Which records the task which is completed on a particular day.

select * from task_activity;

 id |    date    | task_id 
  1 | 2020-01-01 |       4
  2 | 2020-01-02 |       4
  3 | 2020-01-03 |       4
  4 | 2020-01-05 |       4
  5 | 2020-01-06 |       4
  6 | 2020-01-07 |       4
  7 | 2020-01-06 |       5
  8 | 2020-01-07 |       5
(8 rows)

I need to find the streaks of the tasks done.

id |streak| started_on |  ended_on  | task_id
  1 | 3   | 2020-01-01 | 2020-01-03 |  4
  2 | 3   | 2020-01-02 | 2020-01-02 |  4
  3 | 2   | 2020-01-06 | 2020-01-07 |  5

Currently I have tried

  WITH task_done AS(
  SELECT DISTINCT                                        
  id,                                    
  date,task_id,
  RANK() OVER(PARTITION BY task_id ORDER BY date) rank
  FROM task_activity where task_id=4),
                                                        
  streak AS (                                
    SELECT *                                 
    FROM task_done
  ),        
                    
  output AS
  (                      
  SELECT DISTINCT 
  id,streak,
  MIN(date) started_on,
  MAX(date) ended_on
  FROM streak
  GROUP BY 1,2
  )             
     
  SELECT *
  FROM output;

 id |       streak       | started_on |  ended_on  
  1 | (1,2020-01-01,4,1) | 2020-01-01 | 2020-01-01
  2 | (2,2020-01-02,4,2) | 2020-01-02 | 2020-01-02
  3 | (3,2020-01-03,4,3) | 2020-01-03 | 2020-01-03
  4 | (4,2020-01-05,4,4) | 2020-01-05 | 2020-01-05
  5 | (5,2020-01-06,4,5) | 2020-01-06 | 2020-01-06
  6 | (6,2020-01-07,4,6) | 2020-01-07 | 2020-01-07

user_7326738 · Answer

Using a recursive cte:

with recursive cte(d1, d2, id) as (
   select date, date, task_id from tasks
   union all
   select c.d1, t.date, c.id from cte c 
   join tasks t on t.task_id = c.id and c.d2 + interval '1 day' = t.date
)
select row_number() over (order by t1.id), (t1.m + interval '1 day') - t1.d1, t1.* 
from (select c.id, c.d1, max(c.d2) m from cte c group by c.id, c.d1) t1 
where not exists (select 1 from cte c1 where c1.id = t1.id and c1.d1 < t1.d1 and c1.d2 >= t1.m)

Getting the streak of tasks completed

1 Answers