with base as (select distinct OwnerUserId,cast(creationDa...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Literature Meta

Q&A about the site for scholars and enthusiasts of literature

with base as (select distinct OwnerUserId,cast(creationDate as date) as ds from Posts where OwnerUserId>0),

base2 as (select OwnerUserId, ds,lag(ds,1) over (partition by OwnerUserId order by ds) as lag_ds
from base),
base3 as (
select OwnerUserId,ds,lag_ds,datediff(day,lag_ds,ds) as date_diff, 
case when  datediff(day,lag_ds,ds)=1 then 1 else 0 end as is_continious_flag
from base2)

select OwnerUserId,ds,lag_ds,date_diff,is_continious_flag,
sum(is_continious_flag) over (partition by OwnerUserId,is_continious_flag)
from base3

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results