Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table watch_history (user_id integer, created_at date, primary key (user_id, created_at)) ;

insert into watch_history values
(1, '20201207'),(1, '20201206'),(1, '20201203'),(1, '20201202'),(1, '20201201'),(1, '20201130') ;

CREATE TABLE



INSERT 0 6


WITH list AS
(
SELECT user_id
     , array_agg(created_at) OVER (PARTITION BY user_id ORDER BY created_at RANGE BETWEEN CURRENT ROW AND '1 day' FOLLOWING) AS consecutive_days
  FROM watch_history
)
SELECT user_id, count(DISTINCT d.day) AS number_of_consecutive_days
  FROM list
 CROSS JOIN LATERAL unnest(consecutive_days) AS d(day)
 WHERE array_length(consecutive_days, 1) > 1
 GROUP BY user_id


user_id
number_of_consecutive_days


1
6



SELECT 1


WITH list AS
(
SELECT user_id
     , array_agg(created_at) OVER (PARTITION BY user_id ORDER BY created_at RANGE BETWEEN CURRENT ROW AND '1 day' FOLLOWING) AS consecutive_days
  FROM watch_history
)
SELECT user_id
     , array_agg(DISTINCT d.day ORDER BY d.day) AS list_of_consecutive_days
  FROM list
 CROSS JOIN LATERAL unnest(consecutive_days) AS d(day)
 WHERE array_length(consecutive_days, 1) > 1
 GROUP BY user_id


user_id
list_of_consecutive_days


1
{2020-11-30,2020-12-01,2020-12-02,2020-12-03,2020-12-06,2020-12-07}



SELECT 1