1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

declare @phrase as NVarchar(100) = ##Phrase:string##;
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 Id,
           Score,
           AnswerCount,
           case when ClosedDate is null then 0 else 1 end as Closed
    from Posts
    where PostTypeId = 1
      and lower(Body) like lower('%' + @phrase + '%')
      and Id >= @firstid
)
    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
    into #VoteAct
    from Questions left outer join Votes on Questions.Id = Votes.PostId and VoteTypeId in (2,3)
    group by Questions.Id, Score, AnswerCount, Closed
    OPTION (MAXDOP 16)
    ;
select
    @phrase as Phrase,
    (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
    OPTION (MAXDOP 16)

fork query

edited jun 12 14

created jun 12 14 rolfl