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

-- uid:Enter your UserId  "As found in the url of your profile"
; 
with upvQ as (
  select v.creationdate, sum(count(*)*5)
    over (order by v.creationdate) as score
  from posts p 
  inner join votes v on v.postid = p.id
  where v.votetypeid = 2 --UpVote
    and p.posttypeid = 1 -- Q
    and p.owneruserid = ##uid##
  group by v.creationdate)
  , upvA as (
  select v.creationdate
  , sum(count(*)*10)
  over (order by v.creationdate) as score
  from posts p 
  inner join votes v on v.postid = p.id
  where v.votetypeid = 2 --UpVote
  and p.posttypeid = 2 -- A
  and p.owneruserid = ##uid##
  group by v.creationdate)
, dwnVQA as (
  select v.creationdate
  , sum(count(*)*-2)
  over (order by v.creationdate) as score
  from posts p 
  inner join votes v on v.postid = p.id
  where v.votetypeid = 3 --DownVote
  and p.posttypeid in (1,2) -- Q and A
  and p.owneruserid = ##uid##
  group by v.creationdate)
, accA as (
select v.creationdate
     , sum(count(*) * 15) 
     over (order by v.creationdate) as score -- every accepted answer 15 fep
  from posts p 
  inner join votes v on v.postid = p.id
  where v.votetypeid = 1 -- Accept
  and p.posttypeid = 2 -- Answer 
  and  p.owneruserid = ##uid##
  group by  v.creationdate)
, sugedt as (
 -- suggested edit for a user
     select cast(approvaldate as date) as dateonly, 
     sum(count(*) * 2) over (order by cast(approvaldate as date)) as score
     from suggestededits
      where approvaldate is not null
      and owneruserid = ##uid## 
      group by cast(approvaldate as date)
      )
select distinct 
       v.creationdate
     , upvq.score as [Upvote Question]
     , upvA.score as [Upvote Answer] 
     , dwnVQA.score as [Downvotes own Q and A]
     , accA.score as [Accepted Answers]
     , sugedt.score as [Suggested Edits]
from votes v 
left outer join upvQ on upvQ.creationdate = v.creationdate
left outer join upvA on upvA.creationdate = v.creationdate
left outer join dwnVQA on dwnVQA.creationdate = v.creationdate
left outer join accA on accA.creationdate = v.creationdate
left outer join sugedt on sugedt.dateonly = v.creationdate
where v.creationdate >= (
     select creationdate 
     from users 
     where id = ##uid##)
order by v.creationdate

Enter Parameters

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