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
group by PostId

reg_votes as (
select PostId, count(*) reg_vote_count
from Votes
group by PostId

select 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))/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 Posts p
     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 CreationDate < @start
      and Score < 0

Enter Parameters

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