Look for suspicious upvote patterns in a particular tag

4

Please login or register to vote for this query.

(click on this box to dismiss)

Helps find people benefiting from sock puppet or "Admirer" upvotes. For a particular tag get the Avg and StDev of score - suspicious scores are likely to have * BOTH HIGH AVERAGE AND LOW STD DEV * 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.

Engineering

Q&A for professionals and students of engineering

-- Look for suspicious upvote patterns in a particular tag
-- Helps find people benefiting from sock puppet or "Admirer" upvotes. 
-- For a particular tag get the Avg and StDev of score - suspicious scores
-- are likely to have * BOTH HIGH AVERAGE AND LOW STD DEV * 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 [Avg-StDev]
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
ORDER BY AvgScore DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results