Q&A for experienced flyers, pilots, and builders of drones and model aircraft
-- timeframe : Timeframe "yy for year, qq for quarter, mm for month" declare @cutoff integer -- Date from user input --declare @FromDate datetime = Convert(datetime, '##FromDateYYYYMMDD##'); select top 1 @cutoff = v.id from votes v -- Date from user input --where creationdate < @FromDate -- From a year, quarter or month back from now --where creationdate < dateadd(##timeframe?yy##, -1, DATETRUNC(week, getdate())) -- From beginning of this year, quarter or month, -- as it was shown in https://stackoverflow.com/users/current where creationdate < DATEADD(##timeframe?yy##, DATEDIFF(##timeframe?yy##, 0, GETDATE()), 0) 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 ), users_ranked as ( select rank() over(order by [total_rep] desc) [rank] , * from user_scores ) select 'You' , [rank] , [rank] * 100.0 / (select count(*) from users_ranked) [rank_percent] , 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] , [rank] * 100.0 / (select count(*) from users_ranked) [rank_percent] , 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