Stats for Questions from RepUsers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

declare @rep as int = ##Reputation:int?2000##;
declare @epoch as DateTime = DateAdd(Week, - ##Weeks:int?6##, CURRENT_TIMESTAMP);
declare @firstid as int;

select @firstid = min(Id)
from Posts
where CreationDate >= @epoch;

with Questions as (
    select p.Id,
           p.Score,
           p.AnswerCount,
           case when ClosedDate is null then 0 else 1 end as Closed
    from Posts p, Users u
    where PostTypeId = 1
      and OwnerUserId = u.Id
      and p.Id >= @firstid
      and u.Reputation >= @rep
), VoteAct as (
    select Questions.Id,
           Score,
           AnswerCount,
           Closed,
           sum (case when VoteTypeId = 2 then 1 else 0 end) as Upvoted,
           sum (case when VoteTypeId = 3 then 1 else 0 end) as Downvoted,
           count(VoteTypeId) as VoteCount
    from Questions left outer join Votes on Questions.Id = Votes.PostId and VoteTypeId in (2,3)
    group by Questions.Id, Score, AnswerCount, Closed
)
select
    @rep as MinRep,
    (select count(*) from VoteAct) as Matching,
    (select count(*) from VoteAct where Closed > 0) as Closed,
    (select count(*) from VoteAct where DownVoted > 0) as DownVoted,
    (select sum(Score) from VoteAct where DownVoted > 0) as DownvoteTotal,
    (select avg(1.0 * Score) from VoteAct where DownVoted > 0) as DownvotedScore,
    (select count(*) from VoteAct where DownVoted = 0 and UpVoted = 0) as NoVotes,
    (select count(*) from VoteAct where AnswerCount > 0) as Answered,
    (select Avg(1.0 * AnswerCount) from VoteAct where AnswerCount > 0) as AnswerAvg,
    (select count(*) from VoteAct where UpVoted > 0) as UpVoted,
    (select sum(Score) from VoteAct where UpVoted > 0) as UpvoteTotal,
    (select avg(1.0 * Score) from VoteAct where UpVoted > 0) as UpvotedScore

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached