inspired by http://data.stackexchange.com/stackoverflow/s/730/look-for-suspicious-upvote-patterns-in-a-particular-tag
Q&A for Joomla! administrators, users, developers and designers
-- Suspicious upvote pattern v2 -- inspired by http://data.stackexchange.com/stackoverflow/s/730/look-for-suspicious-upvote-patterns-in-a-particular-tag select Users.Id as [User Link], count(*) as count, avg(answers.Score / questions.ViewCount) as score_per_view, stdev(answers.Score / ViewCount) as stddev from ( select cast(Score as numeric) as Score, OwnerUserId, ParentId from Posts where PostTypeId = 2 --answers --and viewCount > 0 -- division by zero ) as Answers inner join ( select Posts.Id, Score, ViewCount from Posts inner join PostTags on Posts.Id = PostTags.PostId inner join Tags on PostTags.TagId = Tags.Id where Posts.PostTypeId = 1 -- questions and CommunityOwnedDate is null -- no communitity posts and Tags.TagName = 'postgresql' ) as Questions on Answers.ParentId = Questions.Id inner join Users on Answers.OwnerUserID = Users.Id group by Users.Id order by count desc