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)

Gardening and Landscaping

Q&A for gardeners and landscapers

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 se.Id
  from SuggestedEdits se
  where se.ApprovalDate is NULL and se.RejectionDate is NULL
),
RepVotes as (
  select 
    'Posts' as ScoreType,
    DT1.Year as Year,
    sum(DT1.Accepts + DT1.Upvotes + DT1.Downvotes) over (ORDER BY DT1.Year) as score
  from
  (
    select 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
    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 ay.Year, 0 as Accepts, 0 as Upvotes, 0 as Downvotes from ActiveYears ay)
  ) as DT1 
), 
SuggEds as (
select 
  'Edits' as ScoreType,
  DT2.Year as Year,
  sum(DT2.Edits) over (ORDER BY DT2.Year) as score
  from
  (
    select 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 ay.Year, 0 as Edits from ActiveYears ay)
  ) 
  as DT2 
),
RepTable as (
  select distinct u.Id, ay.Year as Year, SuggEds.score as Edits, RepVotes.score as Votes, SuggEds.score + RepVotes.score as Reputation, u.Reputation as RealRep
  from ActiveYears ay 
  full outer join SuggEds on SuggEds.Year = ay.Year
  full outer join RepVotes on RepVotes.Year = ay.Year
  full outer join Users u on u.id = ##uid##
  where ay.Year >= Year(u.creationdate)
)
select * 
  from RepTable rt
  where rt.id = ##uid##
  order by Year DESC

Enter Parameters

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