Distribution of vintages among top vote receivers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

With a as
(  -- select votes with date of post and vote
   Select
   p.Id,
   count(p.Id) nId,
   DATEPART(YYYY, p.CreationDate) PostYr, 
   DATEPART(YYYY, v.CreationDate) + 
   1.00*floor((DATEPART(m, v.CreationDate)-1.00)/12*##FreqPerYear##)/##FreqPerYear:int?4## VoteMonth 
   from Posts p join Votes v 
                on p.Id = v.PostId 
   where p.PostTypeId in (1)
     and v.VoteTypeId in (2,3)
   group by
     p.Id,
     DATEPART(YYYY, p.CreationDate),
     DATEPART(YYYY, v.CreationDate) + 
     1.00*floor((DATEPART(m, v.CreationDate)-1.00)/12*##FreqPerYear##)/##FreqPerYear##
), b as
(
   -- give order number per month
   Select
     nId,  
     PostYr,
     VoteMonth,
     count(Id) over (partition by VoteMonth order by nId desc) Porder
   from a
), c as
(  Select
      PostYr,
      VoteMonth,
      count(PostYr) as nYrMnth
   from b 
   where Porder <= ##TopNumber:int?1000##
   group by PostYr, VoteMonth
)

--select * from b

select VoteMonth, CAST(PostYr as Char), log(nYrMnth,10) from c
where VoteMonth > ##MinYear:int?2008## and PostYr > ##MinYear##
  and VoteMonth < DATEPART(YYYY, getDate()) +
                 (DATEPART(m, getDate())-1.00)/12
order by PostYr,VoteMonth

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results