4

I would like to programmatically - or with the API determine what a user's reputation is on a particular day. As such, I have found this link: Determine a user's reputation on a given day

However, it does not outline a programmatic method to do so.

  • 2
    If your particular day doesn't always have to be up to date, you can use SEDE for this. Count their post votes + accepts + suggested edits and sum up. Of course this is slightly inaccurate (going to be a few points to high give or take) since it can't take into account reputation lost from downvoting posts or reputation kept from documentation / deleted posts – Magisch Aug 3 '18 at 6:12
  • Adding to @Magisch's comment, you'll need to include a consideration of the user's bounties as well. Bounties would make a huge difference. – user392547 Aug 3 '18 at 7:23
  • I'm working on a partial solution to this. Depending on how it goes I'll post an answer in an hour or two. – Magisch Aug 3 '18 at 7:31
  • A third approach is also possible: You can ajax AJAX-fetch the user's network profile reputation graph and scrape the highcharts data from the HTML. – Awesome Poodles Aug 3 '18 at 9:33
  • @AwesomePoodles Do note that that approach is only permissible when used for private use and not for profit according to SE's TOS. Otherwise it could get someone IP blocked for attempting. – Magisch Aug 3 '18 at 9:35
8

In addition to writing a program that polls and compiles API data as mentioned by Awesome Poodles' Answer, you can also do this via the Stack Exchange Data Explorer.

There are some limitations due to some events not being visible in the data dump (like private reputation events):

  • The -1 for downvoting posts is not factored in
  • Reputation from the Documentation Beta is not factored in
  • The +2 For accepting answers doesn't obey the rep cap (I had to do this for performance reasons in SEDE)
  • Serial Voting Reversal doesn't factor in (Invisible on SEDE)

Even with these limitations, you can get a pretty representative number for most people. I went ahead and wrote a query doing this here:

http://data.stackexchange.com/stackoverflow/query/882556/reputation-gained-until-a-certain-date

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 5 
  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

For your convenience the UserId And UntilDate can be entered as variables.

  • @AwesomePoodles It does. Votes.VoteTypeId = 8 (Bounty Start) is populated with Votes.UserId And Votes.BountyAmount, so you can grab the total rep offered as bounty like this: 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, – Magisch Aug 3 '18 at 9:32
  • How can I find user reputation for all the user ID's in a Posts table? I have to avoid user input since the total number of questions is pretty large. – Saikat Mondal Jun 6 at 0:45
3

The API provides /users/{id}/reputation-history methods for this. There are 2 flavors:

The first method does not return private reputation events such as the -1 rep for downvoting someone's answer or the super-secret bonus when you {redacted}.

Note: private reputation event data is not available anywhere to anyone but the logged in user viewing her own rep.

Advantages over Data Explorer (SEDE) approaches:

  • Up to the minute versus up to a week stale.
  • Can actually call/use programmatically.
  • ALL reputation types accounted for, including documentation and -- with proper authorization -- private reputation events.
  • Rep-cap handling is baked in, no need to try and kludge a guess for that yourself.

Disadvantages: If a user has more than 1 million reputation events, you won't be able to get all his data within your 10K API quota.


Example:

calling /users/398595/reputation-history?site=meta yields results like:

"items": [ {
  "reputation_history_type": "post_downvoted",
  "reputation_change": -2,
  "post_id": 313519,
  "creation_date": 1533279795,
  "user_id": 398595
}, {
  "reputation_history_type": "post_downvoted",
  "reputation_change": -2,
  "post_id": 313562,
  "creation_date": 1533279038,
  "user_id": 398595
}, {
  "reputation_history_type": "post_upvoted",
  "reputation_change": 5,
  "post_id": 313561,
  "creation_date": 1533278694,
  "user_id": 398595
}, {
  "reputation_history_type": "post_upvoted",
  "reputation_change": 5,
  "post_id": 313561,
  "creation_date": 1533277282,
  "user_id": 398595
}, {
// etc...

-- which you can compile to reconstruct the user's rep on a given date.

  • 1
    small nitpick. You can programmatically call SEDE queries and recieve a .csv of the results. You can also programmatically add parameters to SEDE queries. E.g. data.stackexchange.com/stackoverflow/csv/… will give directly give you a .csv for the results with the given userId and untilDate. Also, the disadvantage isn't really one. If a user has 1 million reputation events, a query will long before time out. – Magisch Aug 3 '18 at 9:12
  • Huh, that csv call to SEDE is new-ish? And, no 1 million reputation events will not time out. And, you have to fetch then 100 at a time (paging, like with all other API calls for "large" data sets). – Awesome Poodles Aug 3 '18 at 9:23
  • Maybe in a better query then mine they wont. But I already had to make compromises with accuracy (+2 accepts not affecting rep cap) in order to get this running. Right now it takes around 30s for Jon Skeet, which presumably has the most reputation events on SO right now, and I think he still doesn't crack 200k events. – Magisch Aug 3 '18 at 9:24
  • @Magisch, Oh, I thought you were talking about the API timing out. Didn't try your query against the Skeetster. – Awesome Poodles Aug 3 '18 at 9:28
  • 1
    Yeah, i meant my query. It's already pretty dangerously close to going into timeout territory due to the full table scan on votes.CreationDate to account for the repcap. It's not indexed afaik and the votes table is super massive and getting larger every week. So the 1 million events caveat for the API doesn't really apply vs a query because if you wanted to run a query to get that many events you'd have to run it on an offline instance of the data dump – Magisch Aug 3 '18 at 9:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .