fraction of early votes per month


Please login or register to vote for this query.

(click on this box to dismiss)

Ukrainian Language Meta

Q&A about the site for linguists, teachers and students of the Ukrainian language

with a as (
   p2.CreationDate as post_date,
   v.CreationDate as vote_date, 
         when VoteTypeId = 2 then 1.00 
         when VoteTypeId = 3 then -1.00 
         else 0.00
    end) Score,
         when DATEDIFF(day, p2.CreationDate, v.CreationDate)
              BETWEEN 0 AND 7
              Then 1
         else 0
    end) early
from Votes v 
join Posts p2 
on p2.Id = v.postId
join ( 
      CAST(cast(DATEPART(YYYY, CreationDate) as float) + cast(DATEPART(MM, CreationDate) as float)/12 as float) [Month],
  from Posts) p 
on p.Id = p2.ParentId 
where VoteTypeId in (2,3)

 sum(case when early = 1 then 1 else 0 end) as "earlyVotes",
 sum(case when early = 0 then 1 else 0 end) as "lateVotes",
 count(early) as "totalVotes"
from a
where month < CAST(CAST(DATEPART(YYYY, getdate()) as float) +
CAST(DATEPART(MM,   getdate()) as float)/12 as float
group by Month
order by Month

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results