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 distinct TOP 30 se.OwnerUserId
  from SuggestedEdits se
  where 
    se.ApprovalDate IS NULL 
    AND se.RejectionDate IS NULL
    AND se.OwnerUserId IS NOT NULL
  order by se.OwnerUserId ASC
),
RepVotes as (
  select
    DT1.OwnerUserId,
    'Posts' as ScoreType,
    DT1.Year as Year,
    sum(DT1.Accepts + DT1.Upvotes + DT1.Downvotes) over (ORDER BY DT1.Year) 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 
  inner join SuggestedEditors sed on sed.OwnerUserId = DT1.owneruserid
), 
SuggEds as (
select
  OwnerUserId,
  'Edits' as ScoreType,
  DT2.Year as Year,
  sum(DT2.Edits) over (ORDER BY 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
      inner join SuggestedEditors sed on sed.OwnerUserId = se.owneruserid
--    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 
),
RepTable as (
  select distinct u.Id, ay.Year as Year, se.score as Edits, rv.score as Votes, se.score + rv.score as Reputation, u.Reputation as RealRep
  from ActiveYears ay 
  full outer join SuggEds se on se.Year = ay.Year 
  full outer join RepVotes rv on rv.Year = ay.Year
--  full outer join Users u on u.id = ##uid##
  inner join SuggestedEditors sed on sed.OwnerUserId = se.OwnerUserId 
  inner join SuggestedEditors sed2 on sed2.OwnerUserId = rv.OwnerUserId
  inner join Users u on u.Id = sed.OwnerUserId
  where ay.Year >= Year(u.creationdate)
)
select * 
  from RepTable rt
--  where rt.id = ##uid##
  order by Id, Year DESC

Enter Parameters

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