Views per action (downvoted questions)


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 @start datetime = (select min(CreationDate) from PostFeedback);

with anon_votes as (
select PostId, count(*) anon_vote_count
from PostFeedback
where VoteTypeId in (2,3)
group by PostId

reg_votes as (
select PostId, count(*) reg_vote_count
from Votes
where VoteTypeId in (2,3)
group by PostId

select Score,
       --count(*) N,
       sum(cast(ViewCount as bigint))/sum(isnull(CommentCount,0)) 'comment ratio',
       sum(cast(ViewCount as bigint))/sum(isnull(AnswerCount,0)) 'answer ratio',
       sum(cast(ViewCount as bigint))/count(distinct ph.Id) 'edit ratio',
       sum(cast(ViewCount as bigint))/sum(isnull(reg_vote_count,0)) 'reg vote ratio',
       sum(cast(ViewCount as bigint))/sum(isnull(anon_vote_count,0)) 'anon vote ratio',
       sum(cast(ViewCount as bigint))
           /(sum(isnull(anon_vote_count,0)) + sum(isnull(reg_vote_count,0))) 'all vote ratio'
      --, sum(cast(ViewCount as bigint))/sum(isnull(FavoriteCount,0)) 'favorite ratio'
from PostsWithDeleted p
     left join PostHistory ph on p.Id = ph.PostId
                                 and PostHistoryTypeId in (4, 5, 6)
                                 and ph.UserId <> p.OwnerUserId
     left join reg_votes r on p.Id = r.PostId
     left outer join anon_votes a on p.Id = a.PostId
where ViewCount is not null
      and PostTypeId = 1
      and p.CreationDate < @start
      and abs(Score) <= 5
group by Score
having sum(isnull(CommentCount,0)) > 0
       and sum(isnull(AnswerCount,0)) > 0
       and sum(isnull(reg_vote_count,0)) > 0
       and sum(isnull(anon_vote_count,0)) > 0
order by Score

Enter Parameters

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