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)

Tor

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 200 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 DESC
),
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
),
SuggEds as (
select
  OwnerUserId,
  DT2.Year as Year,
  sum(DT2.Edits) over (ORDER BY DT2.OwnerUserId, DT2.Year) 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 
)
select * 
  from UserYearRepTable uyrt
  full outer join SuggEds se on se.OwnerUserId = uyrt.OwnerUserId and se.Year = uyrt.Year
  order by uyrt.OwnerUserId, uyrt.Year ASC

Enter Parameters

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