Q&A for researchers, developers, and users of Tor
with ActiveYears as ( select Year, 1 as Edits, 1 as Votes from ( select 2006 as Year union select 2007 union select 2008 union select 2009 union select 2010 union select 2011 union select 2012 union select 2013 union select 2014 union select 2015 union select 2016 union select 2017 union select 2018 union select 2019 union select 2020 union select 2021 union select 2022 union select 2023 union select 2024 union select 2025 union select 2026 union select 2027 union select 2028 union select 2029) as Years where Year <= Year(GETDATE() ) ), SuggestedEditors as ( select distinct TOP 10 se.OwnerUserId, Year(u.CreationDate) as CreationYear, u.Reputation from SuggestedEdits se inner join Users u on se.OwnerUserId = u.Id where se.ApprovalDate IS NULL AND se.RejectionDate IS NULL AND se.OwnerUserId IS NOT NULL order by se.OwnerUserId ASC ), UserYearRepTable as ( select sed.OwnerUserId, ay.Year as Year, sed.Reputation as RealRep from ActiveYears ay inner join SuggestedEditors sed on ay.Year >= sed.CreationYear ), RepVotes as ( select DT1.OwnerUserId, DT1.Year as Year, -- sum(DT1.Accepts + DT1.Upvotes + DT1.Downvotes) over (ORDER BY DT1.Year) as score sum(DT1.Accepts + DT1.Upvotes + DT1.Downvotes) as score from ( select p.owneruserid as OwnerUserId, YEAR(v.creationdate) as Year, (15 * count(case when (v.votetypeid = 1) and (p.posttypeid = 2) THEN 1 ELSE NULL END)) as Accepts, (10 * count(case when (v.votetypeid = 2 and p.posttypeid in (1, 2)) THEN 1 ELSE NULL END)) as Upvotes, ((-2) * count(case when (v.votetypeid = 3 and p.posttypeid in (1, 2)) THEN 1 ELSE NULL END)) as Downvotes from posts p inner join votes v on v.postid = p.id -- inner join SuggestedEditors sed on sed.OwnerUserId = p.owneruserid where v.votetypeid in (1, 2, 3) and p.posttypeid in (1, 2) -- and p.owneruserid = ##uid## group by p.owneruserid, YEAR(v.creationdate) -- union (select ##uid## as OwnerUserId, ay.Year, 0 as Accepts, 0 as Upvotes, 0 as Downvotes from ActiveYears ay) ) as DT1 group by DT1.owneruserid, DT1.Year ), SuggEds as ( select DT2.OwnerUserId, DT2.Year as Year, -- sum(DT2.Edits) over (ORDER BY DT2.Year) as score sum(DT2.Edits) as score from ( select se.owneruserid as OwnerUserId, YEAR(se.creationdate) as Year, (2 * count(case when (se.ApprovalDate IS NOT NULL) THEN 1 ELSE NULL END)) as Edits from suggestedEdits se -- where se.owneruserid = ##uid## group by se.owneruserid, YEAR(se.creationdate) -- union (select ##uid## as OwnerUserId, ay.Year, 0 as Edits from ActiveYears ay) ) as DT2 group by DT2.owneruserid, DT2.Year ), RepTable as ( select uyrt.OwnerUserId, uyrt.Year, uyrt.RealRep, se.score as EditScore, rv.score as VoteScore from UserYearRepTable uyrt left join SuggEds se on se.OwnerUserId = uyrt.OwnerUserId and se.Year = uyrt.Year left join RepVotes rv on rv.OwnerUserId = uyrt.OwnerUserId and rv.Year = uyrt.Year ) select * from RepTable order by OwnerUserId, Year ASC