user reputation history

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/144716/reputation-history-from-data-explorer

CiviCRM Meta

Q&A about the site for administrators and users of the CiviCRM Constituent Relationship Management software

select 
  DT1.Id,
  DT1.Year,
  sum(DT1.Accepts + DT1.Upvotes + DT1.Downvotes) over (ORDER BY DT1.Year)
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 
--  over (order by YEAR(v.creationdate))
  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  
Order by Year ASC

Enter Parameters

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