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
  • ... 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.


1 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")

  • \$\begingroup\$ Ooh, awesome! Let me add a couple of more things based on using that as a test. \$\endgroup\$
    – mattdm
    Apr 2, 2019 at 15:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .