0

Please login or register to vote for this query.

(click on this box to dismiss)

Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática

SELECT
-- Total Reputation
(
SUM(CASE WHEN r2d.ReputationFromVotes + r2d.ReputationFromSuggestedEdits > 200 THEN 200 ELSE r2d.ReputationFromVotes + r2d.ReputationFromSuggestedEdits END) 
+ SUM(r2d.ReputationFromBounties) 
+ COALESCE((SELECT SUM(v4.BountyAmount * -1) FROM Votes AS v4 WHERE v4.VoteTypeId = 8 AND v4.UserId = ##UserId## AND v4.CreationDate < ##UntilDate:string## ),0)
+ COALESCE((SELECT COUNT(*) * 2 FROM Posts AS p3 WHERE p3.OwnerUserId = ##UserId## AND p3.AcceptedAnswerId IS NOT NULL),0)
+ SUM(r2d.ReputationFromAccepts)
) AS TotalReputation,
-- Rep Capped Activities with the Cap Factored in
SUM(
CASE 
WHEN r2d.ReputationFromVotes + r2d.ReputationFromSuggestedEdits > 200 THEN 200
ELSE r2d.ReputationFromVotes + r2d.ReputationFromSuggestedEdits 
END) AS ReputationFromRepCap,
-- Total Bounties recieved
SUM(r2d.ReputationFromBounties) AS ReputationFromBounties,
-- Total Bounties given
COALESCE((SELECT SUM(v4.BountyAmount * -1) FROM Votes AS v4 WHERE v4.VoteTypeId = 8 AND v4.UserId = ##UserId## AND v4.CreationDate < ##UntilDate:string## ),0) AS ReputationGivenAsBounties,
-- Total Reputation from Accepting Answers
COALESCE((SELECT COUNT(*) * 2 FROM Posts AS p3 WHERE p3.OwnerUserId = ##UserId## AND p3.AcceptedAnswerId IS NOT NULL),0) AS ReputationFromAcceptingAnswers,
-- Total Reputation from Accepted Answers
SUM(r2d.ReputationFromAccepts) AS ReputationFromAcceptedAnswers


FROM

(

SELECT 
v.CreationDate AS VoteDate,

-- Total Reputation from Post Upvotes
-- PostTypeId 1 = Question, 2 = Answer
-- VoteTypeId 2 = Upvote, 3 = Downvote
-- CommunityOwnedDate is when a post was made CW. 
-- Votes before that count, after not.
-- Vote Date is truncated to full days only so grouping works
SUM((CASE 
  WHEN (p.PostTypeId = 1 AND v.VoteTypeId = 2 AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)) THEN 10 
  WHEN (p.PostTypeId = 2 AND v.VoteTypeId = 2 AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)) THEN 10 
  WHEN (v.VoteTypeId = 3 AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)) THEN -2
  ELSE 0 
END)) AS ReputationFromVotes,

-- Total Reputation from Answer Bounties
-- VoteTypeId 9 = Bounty Close (Bounty Awarded)
-- BountyAmount = Amount of Reputation awarded
SUM(CASE 
  WHEN v.VoteTypeId = 9 THEN v.BountyAmount
  ELSE 0
END) AS ReputationFromBounties,

-- Total Reputation from Answer Accepts
-- VoteTypeId 1 = AcceptedByOriginator (Answer Accepted)
SUM(CASE
  WHEN (v.VoteTypeId = 1 AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)) THEN 15
  ELSE 0 
END) AS ReputationFromAccepts,

-- Total Reputation from Suggested Edits
-- if ApprovalDate isn't NULL and RejectionDate is NULL it's been approved and not overriden
-- Group by the same Date as Votes for Rep-Cap evaluation (They count towards it)
COALESCE((SELECT
SUM(CASE WHEN (se.ApprovalDate IS NOT NULL AND se.RejectionDate IS NULL) THEN 2 ELSE 0 END)
FROM SuggestedEdits AS se
WHERE se.OwnerUserId = ##UserId##
AND YEAR(v.CreationDate) = YEAR(se.ApprovalDate)
AND MONTH(v.CreationDate) = MONTH(se.ApprovalDate)
AND DAY(v.CreationDate) = DAY(se.ApprovalDate) ),0) AS ReputationFromSuggestedEdits

FROM Posts AS p 
INNER JOIN Votes AS v ON v.PostId = p.Id

WHERE p.OwnerUserId =  ##UserId:int##
AND v.CreationDate <= ##UntilDate:string##

GROUP BY v.CreationDate

) as r2d

fork query

edited aug 14

created aug 14 anonymous