0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows all of the reputation a user has ever earned, and from what actions. It gives a clear indication of how each behavior has contributed to your reputation over time

Q&A about the site for Ubuntu users and developers

-- UserName:Enter a user's display name

Declare @UserId int;
Select @UserId = ##UserId:int##; 
--  (Select Id from Users Where DisplayName = ##UserName:string##);

With
		upvotedQuestions As (
			Select v.CreationDate,
				   Sum(Count(1)*5) Over (Order By v.CreationDate ) As Score
				From Posts p
					 Inner Join Votes v
					On v.PostId = p.id
				Where v.VoteTypeId = 2 -- is an upvote
				  And p.PostTypeId = 1 -- is a question
				  And p.OwnerUserId = @UserId
				Group By v.CreationDate
		)

		, upVotedAnswers As (
			Select v.CreationDate,
				   Sum(Count(1)*10) Over (Order By v.CreationDate) As Score
				From Posts p
					 Inner Join Votes v
					On v.PostId = p.id
				Where v.VoteTypeId = 2 -- is an upvote
				  And p.PostTypeId = 2 -- A
				  And p.OwnerUserId = @UserId
				Group By v.CreationDate
		)

		, downVoteQorA As (
			Select v.CreationDate,
				   Sum(Count(1)*-2) Over (Order By v.CreationDate) As Score
				From Posts p
					 Inner Join Votes v
					On v.PostId = p.id
				Where v.VoteTypeId = 3 -- is a downvote
				  And p.PostTypeId in (1,2) -- Question or Answer
				  And p.OwnerUserId = @UserId
				Group By v.CreationDate
		)

		, acceptedAnswers As (
			Select v.CreationDate,
				   Sum(Count(1) * 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 -- Accepted answer
				  And p.PostTypeId = 2 -- is an answer
				  And  p.OwnerUserId = @UserId
				Group By v.CreationDate
		)

		, suggestedEdit As (
		 -- suggested edit for a user
			 Select Cast(ApprovalDate As date) As dateonly,
					Sum(Count(1) * 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,
				upvotedQuestions.Score As [Upvote Question],
				upVotedAnswers.Score As [Upvote Answer],
				downVoteQorA.Score As [Downvotes own Q And A],
				acceptedAnswers.Score As [Accepted Answers],
				suggestedEdit.Score As [Suggested Edits],
				bounty.start As [Bounties Started],
				bounty.[close] As [Bounties Received]
		From Votes v
			Left Outer Join upvotedQuestions On upvotedQuestions.CreationDate = v.CreationDate
			Left Outer Join upVotedAnswers On upVotedAnswers.CreationDate = v.CreationDate
			Left Outer Join downVoteQorA On downVoteQorA.CreationDate = v.CreationDate
			Left Outer Join acceptedAnswers On acceptedAnswers.CreationDate = v.CreationDate
			Left Outer Join suggestedEdit On suggestedEdit.dateonly = v.CreationDate
			Left Outer Join bounty On bounty.CreationDate = v.CreationDate
		Where v.CreationDate >= '2019-01-01'
		Order By v.CreationDate
    ;

fork query

edited jul 19

created aug 26 14 anonymous