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


Please login or register to vote for this query.

(click on this box to dismiss)

History of Science and Math

Q&A for people interested in the history and origins of science and mathematics

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 order by OwnerUserId,ds) as running_total
from base3

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results