0

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 = (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))/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 Posts 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

fork query

edited mar 21 18

created apr 30 15 Jon "not doe" Ericson