MySQL Calculate Number of Consecutive Days from Earliest Day

Question

I have a dataset that looks like this:

user_id day_session rank    day_diff
xyz789   2017-11-19 1   1
abc123   2017-11-19 1   1
abc123   2017-11-20 2   1
abc123   2017-11-21 3   1
abc123   2017-11-22 4   1
abc123   2017-11-23 5   1
abc123   2017-11-24 6   1
abc123   2017-11-25 7   1
abc123   2017-11-26 8   1
abc123   2017-11-27 9   1
abc123   2017-11-28 10  1
abc123   2017-11-29 11  1
abc123   2017-11-30 12  1
abc123   2017-12-01 13  1
abc123   2017-12-02 14  1
def456   2017-11-19 1   1
def456   2017-11-20 2   1
def456   2017-11-21 3   1
def456   2017-11-22 4   1
def456   2017-11-23 5   1
def456   2017-11-24 6   1
def456   2017-11-25 7   1
def456   2017-11-26 8   1
def456   2017-11-27 9   1
def456   2017-11-28 10  1
def456   2017-11-29 11  1
def456   2017-11-30 12  1
def456   2017-12-01 13  1
def456   2017-12-02 14  1
def456   2017-12-03 15  1
def456   2017-12-04 16  1
def456   2017-12-05 17  1
def456   2017-12-06 18  1
def456   2017-12-07 19  1
def456   2017-12-08 20  1
def456   2017-12-09 21  1
def456   2017-12-10 22  1
def456   2017-12-11 23  1
def456   2017-12-12 24  1
def456   2017-12-13 25  1
def456   2017-12-14 26  1
def456   2017-12-15 27  1
def456   2017-12-16 28  1
def456   2017-12-17 29  1
def456   2017-12-18 30  1
def456   2017-12-19 31  1
def456   2017-12-20 32  1
def456   2017-12-21 33  1
def456   2017-12-22 34  1
def456   2017-12-23 35  1
def456   2017-12-24 36  1
def456   2017-12-25 37  1
def456   2017-12-26 38  5
def456   2017-12-31 39  1
def456   2018-01-01 40  1
def456   2018-01-02 41  1
def456   2018-01-03 42  1
def456   2018-01-04 43  1

I'd like to calculate the number of consecutive day sessions each user_id has from the earliest day_session in the dataset. So user xyz789 would return 1, abc123 would return 14 and def456 would return 38. TIA

Answer 1

For what it's worth, here is a solution using SQL Server:

declare @order table (rowid int identity, user_id varchar(max), day_session datetime, isconsecutive int)



 insert @order

 select distinct user_id, day_session, 

   case 

    when 

     lag(user_id, 1,0) over (order by user_id desc,                                     day_session)=user_id and 

     lag(day_session, 1,0) over 

  (order by user_id desc, day_session)=dateadd(dd,-1,day_session)  

    then 1 

    else 0 

   end 

from #temp 

order by user_id desc, day_session



declare @holding table (user_id varchar(max), consday int, day_session datetime)



declare @iterator int=1 

declare @userid varchar(max) 

declare @userid2 varchar(max) 

declare @isconsecutive int=1 

while @iterator<=(select max(rowid) from @order)

begin   

  select @userid=user_id, @isconsecutive=isconsecutive  

  from @order 

  where rowid=@iterator

if @isconsecutive=0 and 

(select user_id from @order where rowid=@iterator-1)=

(select user_id from @order where rowid=@iterator)

select @userid2=@userid+'_'+cast(@iterator as varchar)



if (select user_id from @order where rowid=@iterator-1)<>(select user_id from @order where rowid=@iterator)

select @userid2=null



 insert @holding

 select isnull(@userid2, @userid) ,  isconsecutive, day_session

 from @order 

 where rowid=@iterator



 set @iterator=@iterator+1

 end 



select user_id, max(cnt) maxconsdays from(

select substring(user_id, 1, len(user_id)-charindex('_', reverse(user_id)))user_id, sum(consday)+1 cnt from @holding

group by user_id)a

group by user_id

order by user_id desc

Answer 2

Based on your data, you can just do:

select t.user, count(*)
from t left join
     (select user, min(day_session) as minds
      from t
      where day_diff > 1
      group by user
     )  tt
     on t.user = tt.user
where tt.minds is null or t.day_session < tt.minds
group by t.user;