data explorer query to find winner of (proposed) new contest

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://photo.meta.stackexchange.com/questions/5899/sql-help-wanted-data-explorer-query-to-find-winner-of-proposed-new-contest/5900#5900

Photography

Q&A for professional, enthusiast and amateur photographers

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results