Simple Reputation Graph

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

-- UserId: User id from profile
-- State: start date in YYYMMDD format 

-- Ignores downvotes (made on and by user), undownvotes, accepted edits, bounty.

WITH dwa AS (
  SELECT v.creationdate, SUM(count(*) * 5)
  OVER (ORDER BY v.creationdate) AS rep
  FROM posts p 
  INNER JOIN votes v ON v.postid = p.id
  WHERE v.votetypeid = 3 -- ?
    AND p.posttypeid = 2 -- Answer
    AND p.owneruserid = ##UserId##
  GROUP BY v.creationdate

), upa AS (
  SELECT v.creationdate
  , SUM(count(*) * 10)
  OVER (ORDER BY v.creationdate) AS rep
  FROM posts p 
  INNER JOIN votes v ON v.postid = p.id
  WHERE v.votetypeid = 2 -- UpVote
    AND p.posttypeid = 2 -- Answer
    AND p.owneruserid = ##UserId##
  GROUP BY v.creationdate
  
), acc AS (
  SELECT v.creationdate
     , SUM(count(*) * 15) 
     OVER (ORDER BY v.creationdate) AS rep
  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
)
  
SELECT DISTINCT 
       v.creationdate
     , dwa.rep AS [Upvote Question]
     , upa.rep AS [Upvote Answer] 
     , acc.rep AS [Accepted Answers]
FROM votes v 
LEFT OUTER JOIN dwa ON dwa.creationdate = v.creationdate
LEFT OUTER JOIN upa ON upa.creationdate = v.creationdate
LEFT OUTER JOIN acc ON acc.creationdate = v.creationdate
WHERE v.creationdate >= cast('##start##' as DateTime)
ORDER BY v.creationdate

Enter Parameters

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