declare @cutoff as DateTime = (select max(CreationDate)-7...

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

declare @cutoff as DateTime = (select max(CreationDate)-7 from Votes);

with ScoreInAWeek as
(
  select p.Id PostId, 
    isnull(count(iif(VoteTypeId=2, 1, null))-count(iif(VoteTypeId=3, 1, null)), 0) Score
  from PostsWithDeleted p
  left join Votes v on p.Id=v.PostId and v.CreationDate < cast(p.CreationDate+7 as Date)
  where p.PostTypeId=1
  and p.CreationDate < @cutoff
  group by p.Id
)

select dateadd(month, datediff(month, 0, p.CreationDate), 0) month,
  count(iif(siaw.Score>0, 1, null))*100.0/count(*) PctPositive,
  count(iif(siaw.Score<=0, 1, null))*100.0/count(*) PctNotPositive,
  count(iif(siaw.Score<0, 1, null))*100.0/count(*) PctNegative
from PostsWithDeleted as p
join ScoreInAWeek siaw on siaw.PostId=p.Id
where p.PostTypeId = 1 -- question
group by dateadd(month, datediff(month, 0, p.CreationDate), 0)
order by dateadd(month, datediff(month, 0, p.CreationDate), 0)

Enter Parameters

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