I want to get the number of consecutive days from the current date using Postgres SQL

enter image description here

Above is the scenario in which I have highlighted consecutive days count should be like this. Below is the SQL query which I have created but it's not returning the expected result

with grouped_dates as (
  select user_id, created_at::timestamp::date, 
         (created_at::timestamp::date - (row_number() over (partition by user_id order by created_at::timestamp::date) || ' days')::interval)::date as grouping_date
  from watch_history
)
select * , dense_rank() over (partition by grouping_date order by created_at::timestamp::date) as in_streak
from grouped_dates where user_id = 702
order by created_at::timestamp::date

Can anyone please help me to resolve this issue?

If anyhow we can able to apply distinct for created_at field to below query then I will get solutions for my issue.

WITH list AS
(
SELECT user_id,
  (created_at::timestamp::date - (row_number() over (partition by user_id order by created_at::timestamp::date) || ' days')::interval)::date as next_day
  FROM watch_history
)
SELECT user_id, count(*) AS number_of_consecutive_days
  FROM list
 WHERE next_day IS NOT NULL
 GROUP BY user_id

can anyone have idea how to apply distinct to created_at for above mentioned quey ?

CodePudding user response：

To get the "number of consecutive days" for the same user_id :

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

To get the list of "consecutive days" for the same user_id :

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

full example & result in dbfiddle