Find people USING sock puppets. For a particular tag get the Avg and StDev of score - people who routinely log on using other accounts to upvote themselves likely have high avg AND low StDev of score (e.g. they routinely upvote each of their own posts x times). To filter out noise look for > 10 posts and high average as persistent cheaters are likely to be... persistent! *** NOT AN ABSOLUTE TEST *** - human needs to read some posts and verify.
Q&A about the site for expert genealogists and people interested in genealogy or family history
-- Find sock puppets! Look for suspicious upvote patterns in a particular tag -- Find people USING sock puppets. -- For a particular tag get the Avg and StDev of score - people who routinely -- log on using other accounts to upvote themselves likely have high avg AND low StDev of -- score (e.g. they routinely upvote each of their own posts x times). -- To filter out noise look for > 10 posts and high average -- as persistent cheaters are likely to be... persistent! -- *** NOT AN ABSOLUTE TEST *** - human needs to read some posts and verify. SELECT Users.Id, Users.Id As [User Link], COUNT(Posts.Id) As NumPosts, SUM(Posts.Score) As SumScore, AVG(CAST(Posts.Score as FLOAT)) As AvgScore, STDEV(Posts.Score) As StdDevScore, AVG(CAST(Posts.Score as FLOAT)) - STDEV(Posts.Score) As XXX FROM Posts INNER JOIN Users ON Posts.OwnerUserID = Users.Id WHERE Posts.PostTypeID = 2 AND Posts.ParentId IN ( -- Posts with a particular tag SELECT Posts.Id FROM Posts INNER JOIN PostTags ON Posts.Id = PostTags.PostId INNER JOIN Tags ON PostTags.TagId = Tags.Id WHERE Posts.PostTypeID = 1 AND Tags.TagName = '##tagname##' ) GROUP BY Users.Id, Users.DisplayName HAVING COUNT(Posts.Id) >= 10 AND AVG(Posts.Score) >=1 AND AVG(CAST(Posts.Score as FLOAT)) - STDEV(Posts.Score) > 1 ORDER BY AvgScore DESC