with timeevents as ( select creationdate , len(body) as l...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

with timeevents as
(
select creationdate
     , len(body) as length
     , case 
       when posttypeid = 1 then 'Q'
       when posttypeid = 2 then 'A'
       end as type
from posts
where owneruserid = ##userid:int##
union
select creationdate
     , len([text]) 
     , 'C'
from comments
where userid = ##userid:int##
)
, numbered as (
select row_number() over(order by creationdate) as eventnum
     , creationdate
     , length
     , type
from timeevents
)
, minutes_between_events as (
select datediff(n, s.creationdate, e.creationdate) as span
     , cast(s.eventnum as nvarchar) 
     + ' - ' 
     + cast(e.eventnum as nvarchar) as evm
     , s.length + e.length as totlen
     , s.type + e.type as types
from numbered s
inner join numbered e on s.eventnum + 1 = e.eventnum 
)

select count(*) as [hours lost of your life]
, count(*) / 24 as [days lost of your life]
, sum(span)
from  minutes_between_events 
where span < 60

Enter Parameters

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