0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for aircraft pilots, mechanics, and enthusiasts

With a as
(
   Select top 10000
   p.Id,
   cast(DATEPART(YYYY, p.CreationDate) as char) TimeYr, 
   p.PostTypeId,
   case when VoteTypeId = 2 then 1.00 
        when VoteTypeId = 3 then -1.00 else 0.00 end VoteS,
   DATEDIFF(day, p.CreationDate, v.CreationDate) delay
   from Votes v 
   join Posts p 
   on p.Id = v.PostId 
   where p.PostTypeId in (1)
     and VoteTypeId in (2,3)
     and cast(DATEPART(YYYY, p.CreationDate) as int) 
                                    in (2008)
   
), c as 
(
   Select TimeYr, count(distinct Id) nyear from a
   where PostTypeId = 1 
   group by TimeYr
), d as
(
   Select
     round(1.00*delay/30, 0) mdelay, 
     TimeYr,  
     SUM(VoteS) sumscore
   from a
   where PostTypeId = 1 
         and delay <= 36
         and Cast(TimeYr as int) in (2008,2010,2012,2014,2016)
   group by round(1.00*delay/30, 0), TimeYr
)


Select 
d.mdelay, d.TimeYr, (d.sumscore/c.nyear)
from d join c on d.TimeYr = c.TimeYr
order by d.TimeYr, d.mdelay

view current version

edited nov 19 19

created oct 29 19 anonymous