4

I am doing a project on user behavior on StackOverflow and found a discrepancy of 538,756 when I summed post scores minus the number of up votes plus the number of down votes.

  • Are votes that are reverted because of vote fraud left in the database?
  • Are votes on suggested edits included in the Votes table? If so, is there any way to differentiate between votes on posts themselves and votes on edits of them?

Thank you!

  • I am not familiar with the data dumps, but I think it might help if you state where you got the numbers of votes from and what the sign of the discrepancey is. (Also, as a wild guess: votes on deleted posts.) – Wrzlprmft Nov 12 '14 at 14:57
5

I'm not sure about the data-dump but on SEDE the Votes table holds also the votes on deleted posts.

select votetypeid, count(*)
from votes
left outer join posts p on p.id = votes.postid
where p.id is null
and votetypeid in (2,3)
group by votetypeid

So that explains at least one difference.

On SEDE the votes on suggestededits are in the suggestededitvotes table. So they are not in the Votes table. As found in the data dictionary.

If I filter strict on question and answer posts (posttypeid 1 and 2) and on upmod and downmod votes (votetypeid 2 and 3) and only take existing posts I get much closer to a consistent outcome:

select sum(score) as score
,      sum(upmod) as up
,      sum(downmod) as down
,      sum(score) - sum(upmod) + sum(downmod) as diff
from (
   select sum(score) as score
        , 0 as upmod
        , 0 as downmod
   from posts p
   where p.posttypeid in (1,2)
   union
   select 0
   , sum(case when v.votetypeid = 2 then 1 else 0 end) as upmod
   , sum(case when v.votetypeid = 3 then 1 else 0 end) as downmod
   from posts p
   inner join votes v on v.postid = p.id
   where p.posttypeid in (1,2)
   and v.votetypeid in (2,3)
) as total

Gives this result for the dump from Nov 9 at 5:47:

score    | up       |  down   | diff
------------------------------------
44718052 | 47117512 | 2398910 | -550

The difference is probably explained by last minutes changes that were not commited to the table when the backup ran as you can tell by looking at the Timeline of the posts where the score in posts table doesn't match the number of upvotes/downvotes in the Votes table:

select p.score
, p.posttypeid
, p.id as [Post Link]
,  'site://posts/' + 
  case when posttypeid = 1 then CAST(p.Id AS nvarchar) 
  else CAST(p.parentId AS nvarchar)   end + '/timeline|Timeline' as timeline
from posts p 
inner join (
select p.id
   , sum(case when v.votetypeid = 2 then 1 else 0 end) as upmod
   , sum(case when v.votetypeid = 3 then 1 else 0 end) as downmod
   from posts p
   inner join votes v on v.postid = p.id
   where p.posttypeid in (1,2)
   and v.votetypeid in (2,3)
   group by p.id
   ) pv on pv.id = p.id
   where p.posttypeid in (1,2) 
   and p.score <> pv.upmod - pv.downmod

All timelines for the involved posts have changes on November, 9th ...

You must log in to answer this question.

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