I am running this query on the Stack Exchange Data Explorer (for Stack Overflow):

select owneruserid
 , row_number() over(order by rep desc) as ranking
 , rep
from (
select p.owneruserid
 ,sum(case v.votetypeid when 2 then -- upvote
       case p.posttypeid when 1 then 5 -- Q
                         else 10 end -- A
       else 0 end)
   + sum(case v.votetypeid when 3 then -2 else 0 end) -- downvote
   + sum(case v.votetypeid when 1 then 15 else 0 end) as rep -- accept
from posts p
inner join votes v on v.postid = p.id
inner join users u on u.id = p.owneruserid
where v.creationdate between ##start:string?2018-01-01##
                     and ##end:string?2018-12-31##
and   v.votetypeid in (1,2,3)
and p.owneruserid is not null
group by p.owneruserid
) as rawdata
order by ranking

I aim to get the users ordered by their reputation on year 2018.

The problem is that the Stack Exchange website that corresponds on year 2018 returns quite different results. Top Users of 2018

Am I doing something wrong here or is it an issue on Data Explorer?


It's ... hard ... to calculate reputation with SEDE.

The query does not take into account the reputation cap (200 per day from up/downvotes), nor does it account for bounties (offered/earned).

Slightly less important: the +2 reputation you get from accepting an answer; the -1 from downvoting an answer and the +2 suggested edits to posts (unlikely) and tag wikis (somewhat more likely).

  • @Glorfinder I think you are right. I need to take into account the bounties as well. The problem is how I can determine the amount of the bounty? – Iraklis Moutidis Jan 31 '19 at 21:45
  • @Anoroah Not to mention users who kept reputation from deleted posts, which isn't something you can look up on SEDE. We've had people contact us about trying to run these calculations, and we generally tell them they can get close but if they want exact numbers, don't bother. It's not possible to calculate the exact numbers. You don't have access to all the necessary information there. – animuson Feb 1 '19 at 0:03
  • @animuson Thank you for your reply. Is there any option to obtain such information? I am currently crawling this data but I am looking for a "cleaner" way to do so. – Iraklis Moutidis Feb 1 '19 at 0:05
  • @Anoroah Unless you convince our team to release data for academic research, no, there isn't. – animuson Feb 1 '19 at 0:06
  • @animuson I got in touch with your team and they said that I can get this information from SEDE and from the SE archive. I processed the data from the archive and return the same results with SEDE. So I am back to square one again. – Iraklis Moutidis Jun 6 '19 at 21:05

You must log in to answer this question.

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