Distribution of score aquired as function of age for questions and answers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Aviation

Q&A for aircraft pilots, mechanics, and enthusiasts

With a as
(
   Select 
   p.Id,
   cast(DATEPART(YYYY, p.CreationDate) as char) TimeYr, 
   p.PostTypeId,
   p.Score,
   VoteTypeId,
   round(1.00*DATEDIFF(day, p.CreationDate, v.CreationDate)/30,0) delay
   from Votes v 
   join Posts p 
   on p.Id = v.PostId 
   where p.PostTypeId in (1,2) and VoteTypeId in (2,3)
), b as 
(
select 
   Id,
   TimeYr,
   PostTypeId,
   Score,
   VoteTypeId,
   delay, 
   SUM(case when VoteTypeId = 2 then 1.00 when VoteTypeId = 3 then -1.00 else 0.00 end) SumScore
from a
group by Id, Timeyr,
   PostTypeId,
   Score,
   VoteTypeId,
   delay
), c as 
(
   Select TimeYr, count(distinct Id) nyear from b
   where PostTypeId = 1 
   group by TimeYr
)

Select
delay, b.TimeYr, log(SUM(1.00*SumScore/(nyear*1.00)),10) avgscore
from b join c on b.TimeYr = c.TimeYr
where PostTypeId = 1 
      and delay <= 36
      and Cast(b.TimeYr as int) in (2010,2011,2012,2015)
group by delay,b.TimeYr
order by TimeYr, delay

Enter Parameters

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