1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

select OwnerUserId [User Link]
,      CreationDate
,      days_between_posts
,      RW=ROW_NUMBER() OVER( PARTITION BY GRP  ORDER BY OwnerUserId, CreationDate)
from   ( select OwnerUserId
         ,      CreationDate
         ,      datediff(d, lag(CreationDate) over (order by OwnerUserId, CreationDate), CreationDate) days_between_posts
         ,      DATEDIFF(d, '1900-01-01' , CreationDate)- ROW_NUMBER() OVER(partition by OwnerUserId ORDER BY CreationDate ) AS GRP
         from   ( select OwnerUserId
                  ,      cast(CreationDate as date) CreationDate
                  from   Posts p
                  where  OwnerUserId > 1
                  group
                  by    OwnerUserId
                  ,     cast(CreationDate as date)
                ) p
         group
         by     OwnerUserId
         ,      CreationDate
       ) cd
where  1=1
order
by     OwnerUserId
,      CreationDate

fork query

edited mar 28 17

created mar 28 17 Patrick Hofman