with ActiveYears as ( select Year, 1 as Edits, 1 as Votes...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Motor Vehicle Maintenance and Repair

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

Enter Parameters

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