User Virtual Meta Reputation chart for the past year

1

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

SELECT
  SUM(CASE
    WHEN v.VoteTypeId = 1 THEN 15
    WHEN v.VoteTypeId = 2 AND p.PostTypeId = 1 THEN 5
    WHEN v.VoteTypeId = 2 AND p.PostTypeId = 2 THEN 10
    WHEN v.VoteTypeId = 3 THEN -2
  END) AS Reputation,
  COUNT(DISTINCT(CASE WHEN p.PostTypeId = 1 THEN p.Id END)) AS Questions,
  COUNT(CASE WHEN v.VoteTypeId = 2 AND p.PostTypeId = 1 THEN 1 END) AS QUpVotes,
  COUNT(CASE WHEN v.VoteTypeId = 3 AND p.PostTypeId = 1 THEN 1 END) AS QDownVotes,
  (CONVERT(int, COUNT(CASE WHEN v.VoteTypeId = 2 AND p.PostTypeId = 1 THEN 1 END)) + 1) / (CONVERT(int, COUNT(CASE WHEN v.VoteTypeId = 3 AND p.PostTypeId = 1 THEN 1 END)) + 1) AS QVoteRatio,
  COUNT(DISTINCT(CASE WHEN p.PostTypeId = 2 THEN p.Id END)) AS Answers,
  COUNT(CASE WHEN v.VoteTypeId = 2 AND p.PostTypeId = 2 THEN 1 END) AS AUpVotes,
  COUNT(CASE WHEN v.VoteTypeId = 3 AND p.PostTypeId = 2 THEN 1 END) AS ADownVotes,
  (CONVERT(int, COUNT(CASE WHEN v.VoteTypeId = 2 AND p.PostTypeId = 2 THEN 1 END)) + 1) / (CONVERT(int, COUNT(CASE WHEN v.VoteTypeId = 3 AND p.PostTypeId = 2 THEN 1 END)) + 1) AS AVoteRatio,
  COUNT(CASE v.VoteTypeId WHEN 1 THEN 1 END) AS Accepted
FROM PostsWithDeleted p
  INNER JOIN Votes v ON p.Id = v.PostId
  INNER JOIN Users u ON p.OwnerUserId = u.Id
WHERE u.Id = ##UserId:int##
  AND v.CreationDate > DATEADD(year,-1,GETDATE())

Enter Parameters

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