0

Please login or register to vote for this query.

(click on this box to dismiss)

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 STDEV(Posts.Score) <= .5
ORDER BY AvgScore DESC

fork query

edited jul 12 12 KronoS

created dec 17 10 Ryan