with repvotes as ( select DT1.Id as Id, 'Posts' as ScoreT...

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 repvotes as (
select 
  DT1.Id as Id,
  '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 Id, 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)
  ) as DT1 ), 
edits as (
select 
  DT2.Id as 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  
  where se.owneruserid = ##uid##
  group by se.owneruserid, YEAR(se.creationdate)
  ) as DT2 )
select u.id, u.reputation, *
from users u where u.id = ##uid##

Enter Parameters

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