Q&A for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles
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 Top 50 se.Id from SuggestedEdits se where se.ApprovalDate is NULL and se.RejectionDate is NULL ), SuggEds as ( select DT2.Id, 'Edits' as ScoreType, DT2.Year as Year, sum(DT2.Edits) over (ORDER BY DT2.Year) as score from ( select se.owneruserid as Id, YEAR(se.creationdate) as Year, (2 * count(case when (se.ApprovalDate IS NOT NULL) THEN 1 ELSE NULL END)) as Edits from suggestedEdits se left join SuggestedEditors sed ON sed.Id = se.owneruserid -- where se.owneruserid = ##uid## group by se.owneruserid, YEAR(se.creationdate) -- union (select ay.Year, 0 as Edits from ActiveYears ay) ) as DT2 ), RepTable as ( select distinct u.Id, ay.Year as Year, SuggEds.score as Edits, SuggEds.score as Reputation, u.Reputation as RealRep from ActiveYears ay full outer join SuggEds on SuggEds.Year = ay.Year full outer join Users u on u.id in (select Id from SuggestedEditors) where ay.Year >= Year(u.creationdate) ) select * from RepTable rt where rt.id in (select Id from SuggestedEditors) order by Id, Year ASC