How to get number of consecutive days from current date using postgres?

user_13354300 0
Viewed 163

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

Does anyone have an idea how to apply distinct to created_at for the above mentioned query ?

amazon-rds postgresql
1 Answers

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

user_80600170
Related