My reputation graph

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

-- UserId:Enter a 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 = ##UserId##
  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 = ##UserId##
  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 = ##UserId##
  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 = ##UserId##
  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 = ##UserId## 
    GROUP BY cast(approvaldate AS date)

), bounty AS (
	SELECT  v.creationdate
		, SUM(SUM( CASE WHEN votetypeid =8 THEN bountyamount ELSE 0 END)) 
		OVER (ORDER BY v.creationdate) AS [start] 
		,SUM( SUM( CASE WHEN votetypeid =9 THEN bountyamount ELSE 0 END))
		OVER (ORDER BY v.creationdate) AS [close]
    FROM votes v
    INNER JOIN posts p ON p.id = v.postid 
    WHERE votetypeid IN (8,9)
    AND ((p.posttypeid = 2 AND votetypeid =9)
    OR (p.posttypeid = 1 AND votetypeid =8))
    AND v.userid= ##UserId##
    GROUP BY v.creationdate
)
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]
     , bounty.start AS [Bounties Started]
     , bounty.[close] AS [Bounties Received]
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
LEFT OUTER JOIN bounty ON bounty.creationdate = v.creationdate
WHERE v.creationdate >= cast('2015-06-01' as DateTime)
ORDER BY v.creationdate

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results