Q&A for music historians, critics, and fans
-- timeframe : Timeframe "yy for year, qq for quarter, mm for month" declare @cutoff integer select top 1 @cutoff = v.id from votes v where creationdate < dateadd(##timeframe?yy##, -1, DATETRUNC(week, getdate())) order by creationdate desc select id, displayname into #users from users where reputation > 200 create index #ix_users on #users(id) ;with user_scores as ( select u.id , sum( case v.votetypeid when 1 then 15 when 2 then 10 when 3 then -2 when 9 then v.bountyamount else 0 end ) [total_rep] from #users u inner join posts p on p.owneruserid = u.id and p.communityowneddate is NULL inner join votes v on v.postid = p.id and votetypeid in (1,2,3,9) where v.id > @cutoff group by u.id ), user_bounties as ( select u.id , sum(-v.bountyamount) [bounty_rep] from #users u inner join votes v on v.userid = u.id where v.id > @cutoff and votetypeid = 8 group by u.id ), users_ranked as ( select rank() over(order by (us.[total_rep] + ub.[bounty_rep]) desc) [rank] , us.[total_rep] + ub.[bounty_rep] [total_rep] , us.id from user_scores us inner join user_bounties ub on ub.id = us.id ) select 'You' , [rank] , concat('site://users/', r.id, '|', u.displayname) [Link] , [total_rep] from users_ranked r inner join #users u on u.id = r.id where r.id = ##userid## union all select 'Community' , [rank] , concat('site://users/', r.id, '|', u.displayname) [Link] , [total_rep] from users_ranked r inner join #users u on u.id = r.id order by 1 desc, [total_rep] desc