SQL help wanted: data explorer query to find winner of (proposed) new contest

Question

See Proposal: Rules for *New* Photo Contest on Main Site.

We need a https://data.stackexchange.com query which will show the current winner. Specifically:

• questions in the previous Sunday-to-Saturday period
• ... tagged with contest-and-critique
• ... which are still open
• ... ranked by number of upvotes (ignoring downvotes).

... showing rank, number of upvotes, number of downvotes (for informational purposes only), and then a link to the question.

Answer 1

Without data it is a bit of a gamble but I believe this query will cut it, if you run it in the week directly after the contest:

declare @sd date
declare @ed date

-- sunday calculation based answer https://stackoverflow.com/a/21784349
-- from Kakkarot
set @sd =dateadd(d
, -(7*##numofweeks:int?4##)-(1+((5 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7))
          ,  convert(date,getdate()) 
          )

set @ed =dateadd(d
, -(1+((5 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7))
          ,  convert(date,getdate()) 
          )

print @sd
print @ed

select rank() over(order by count(*) desc) [rank]
     , p.id as [Post Link]
     -- , p.creationdate
from posts p
inner join posttags pt on pt.postid = p.id
inner join tags t on t.id = pt.tagid
inner join votes v on v.postid = p.id 
                   and votetypeid = 2 -- up mod
where t.tagname =##tag:string?lens## -- contest-and-critique
and p.creationdate 
    between @sd
    and @ed
group by p.id
order by 1 asc

Tagname and number of weeks to take into account is parameterized. I could adapt it if you rather have it show the ranking per week but then you need to specify if votes received after the winner is announced should still count. At the moment this query doesn't take that into account.

Feel free to hop into SEDE Chat for discussion (or just say "Hi")