9

According to the Database schema documentation for SEDE, the table Votes contains as VoteTypeId 4 = Offensive and 12 = Spam. The latter was mentioned, for example, here: How to find posts closed as spam in the Stack Exchange Data Explorer (SEDE)?

What exactly do votes of these types mean?

Originally I thought that it registers every flag of the corresponding type (spam or rude/abusive). But I see that for most posts there is only a single entry. You can see the number in this query. And here are the recent posts with such votes.

At the same time, there are some posts which have several votes of this type - so it is not that this is registered only if the post is deleted as spam. (Moreover, there are some posts with such votes which were never deleted.)

According to the FAQ posts on spam flags, a post is deleted after four red flags. I would expect that spam is more often removed by regular users than by moderators - so I would expect majority of posts having several spam votes/flags. But, for example, for SO the above query returns only 560 posts with more than one flag (out of more than 50,000 posts).

Of course, with my limited knowledge of SQL, I cannot rule out a possibility that I simply mixed-up something when creating those queries.

2
  • Remember that when a moderator casts such a flag, the post is deleted as if it reached the threshold number of flags. Commented Jul 25, 2023 at 7:18
  • @SonictheAnonymousHedgehog Yes, that is true. But looking at the query for SO, only about 500 posts have more than one vote - and there is more that 50,000 of them. I think it is reasonable to assume that regular users typically remove most of the spam before a moderator gest to see the post. (Otherwise moderators would basically have to hunt for the spam posts 24/7.)
    – Martin
    Commented Jul 25, 2023 at 7:20

2 Answers 2

12
+50

It looks like these are just Community's votes to delete posts as spam or abusive, which happens after 1 moderator red flag or N red flags from other users (currently 4 but usually 6). The number returned by the query almost exactly matches the number returned in the mod-only search locked:yes deleted:yes migrated:no (which itself would include additional locks, such as comment only locks).

The undeleted posts I looked at were red flag deleted, but were restored by a moderator.

When there are multiple such votes on a post, it's an indication that either:

  • After the post was red flag deleted, a moderator cleared the flags and the post was red flag deleted again afterwards.

  • A race condition happened, where two people cast the "final" flag in the same millisecond and it got double deleted/locked. Here is an example from a period when it only took three red flags to delete on ELU, but four flags were cast (Note that on red flags, the top timestamp is when the flag was cast and the bottom one is when it was cleared. There are two groups of events that are "S"imultaneous here, with the other set being the first four timestamps):

    four spam flags, last two cast simultaneously and both causing the post to be deleted/locked

    Those blue votes are cast by Community in response to the appropriate number of flags. You can't see them unless you're a moderator.

2
  • 2
    It's possible we intentionally only show Community votes to obscure the names of people who flagged the posts.
    – Catija
    Commented Jul 25, 2023 at 14:41
  • I am 95% sure Catija is right (as usual). The views that get used to filter Prod data to populate SEDE works hard to anonymize votes & flags that would normally be anonymous via the site. There is almost certainly a criteria on the relevant one for flags of UserId = -1
    – AMtwo
    Commented Aug 3, 2023 at 19:52
0
  • VoteTypeId=4 (Offensive) This is used to flag a post as offensive or containing inappropriate content. It's basically when someone raises an offensive flag.
  • VoteTypeId=12 (Spam) About the same as VoteTypeId=4, this is used to flag a post as spam. Basically when someone raises a spam flag.

You are correct that for most posts, there is only one entry for offensive or spam votes in the Votes table. This is because it records individual votes cast by users, and not than the total number of flags or reports for a specific post.

And as mentioned in the comments, the queries you linked to may not show all flags or reports for a post.

1
  • 7
    I don't really understand this answer. When you say that this is "someone raises a spam flag" and it "records individual votes cast by users", I would expect that majority of the posts should have several entries. (Since several spam/offensive flags are needed to remove a post.)
    – Martin
    Commented Jul 25, 2023 at 8:54

You must log in to answer this question.

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