Most voted answers in past week

0

Please login or register to vote for this query.

(click on this box to dismiss)

Programming Puzzles and Code Golf

Q&A for programming puzzle enthusiasts and code golfers

declare @todate as date = DATEADD(DAY, 0 - DATEPART(WEEKDAY, CURRENT_TIMESTAMP), Convert(DATE, CURRENT_TIMESTAMP))
;
print @todate
;
declare @fromdate as date = DateAdd(day, -7, @todate)
;
print @fromdate
print @todate
;
declare @totalcount as int

;

    select @totalcount = count(*)
    from Posts p,
         Votes v
    where p.Id = v.PostId
      and v.VoteTypeId in (2, 3) -- upvote/downvote
      and p.PostTypeId = 2 -- answer
      and v.CreationDate >= @fromdate
      and v.CreationDate <  @todate

    select ROW_NUMBER () over( order by count(*) desc) as Rank,
           p.Id as [Post Link],
           count(*) as [Answer Upvotes],
           @totalcount as [Week Vote Total]
    from Posts p,
         Votes v
    where p.Id = v.PostId
      and v.VoteTypeId in (2, 3) -- upvote/downvote
      and p.PostTypeId = 2 -- answer
      and v.CreationDate >= @fromdate
      and v.CreationDate <  @todate
    group by p.Id
    order by count(*) desc

Enter Parameters

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