0

Please login or register to vote for this query.

(click on this box to dismiss)

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, (se.score + rv.score) as TotalScore 
  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 OwnerUserId, Year, RealRep, EditScore, VoteScore, TotalScore from RepTable
order by OwnerUserId, Year ASC

fork query

edited oct 14

created oct 14 PeterJames