0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for developers and end users of the Sitecore CMS and multichannel marketing software

With a as
(
   Select
   p.Id,
   DATEPART(YYYY, p.CreationDate) 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 v.VoteTypeId in (2,3)
     and DATEPART(YYYY, p.CreationDate) in (2010,2012,2014,2016)
   
), c as 
(
   Select DATEPART(YYYY, CreationDate) TimeYr,
          count(Id) nyear from Posts
   where PostTypeId = 1 
   group by DATEPART(YYYY, CreationDate)
), d as
(
   Select
     floor(1.00*delay/30) mdelay, 
     TimeYr,  
     SUM(VoteS) sumscore
   from a
   group by floor(1.00*delay/30), TimeYr
)


Select 
  d.mdelay+1,
  CAST(d.TimeYr as Char) year, 
  --d.sumscore,
  --c.nyear,
  (d.sumscore/c.nyear) averagescore
from d join c on d.TimeYr = c.TimeYr
where d.mdelay >= 0 and d.mdelay <= 36
order by d.TimeYr, d.mdelay

fork query

edited nov 19

created oct 29 anonymous