Views per action

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for 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 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
     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 CreationDate < @start

Enter Parameters

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