4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

declare @start datetime = '2017-10-08';

with terms as(
select iif(ClosedDate <= CreationDate+3 /*and IsClosedAsDuplicate = 0*/, 1, 0) closed,
       iif(DeletionDate <= CreationDate+3, 1, 0) deleted,
       iif(Score > 0, 1, 0) upvoted,
       iif(Score < 0, 1, 0) downvoted,
       iif(Score = 0, 1, 0) even,
       iif(AnswerCount > 0, 1, 0) answered,
       
       Id PostId
  from PostsWithDeleted
  where PostTypeId = 1
  
)

select dateadd(week, datediff(week, @start, CreationDate), @start) week,
       sum(iif(closed = 1 or downvoted = 1, 100.0, 0))/count(*) bad,
       sum(iif(even = 1 or answered = 0, 100.0, 0))/count(*) neutral,
       sum(iif((answered = 1 and even = 1) or upvoted = 1, 100.0, 0))/count(*) good,
       sum(iif(closed = 1 or downvoted = 1 or deleted = 100.0, 1.0, 0))/count(*) bad_deleted,       sum(downvoted)/count(*) downvoted,
       sum(downvoted)*100.0/count(*) downvoted,
       sum(closed)*100.0/count(*) closed,
       sum(deleted)*100.0/count(*) deleted,
       sum(answered)*100.0/count(*) answered       
       
from terms 
     join PostsWithDeleted on Id = PostId
where PostTypeId = 1
group by dateadd(week, datediff(week, @start, CreationDate), @start)
order by dateadd(week, datediff(week, @start, CreationDate), @start)

view current version

edited nov 1 17

created oct 27 17 Jon "not doe" Ericson