Q&A about the site for practitioners of the art and science of signal, image and video processing
-- Attempts to correlate users that have either -- given or recieved an undue amount of their accepted answer -- reputation from specific others. This is an attempt to detect -- Voting ring activity or other targeted voting based on the principle -- that users engaging in voting ring activity have a higher then average -- rate of inter-ring accept votes. -- IMPORTANT: Serial Voting and Sockpuppetry can only be actioned -- and investigated by moderators or employees of Stack Exchange. This is not -- a tool to single out users for harassment or to start witch hunting. -- Please exercise extreme caution and discretion when using this for flagging -- or reporting. -- Version 0.1 (Concept Stage) -- Parameters -- MaxReputation: Users exceeding this amount of reputation are excluded. -- SearchStartDate: ... starting at -- SearchLookupLength: Looks for this many days in the past ... -- ReputationTransferCoupled: How much reputation needs to have been exchanged -- MinimumAccepts: This many posts need to be accepted. -- Select all the votes to draw from... SELECT Votes.Id AS [Vote_ID], Votes.CreationDate AS [Vote_Date], Questions.Id AS [Question_ID], Answers.Id AS [Answer_ID], Questions.OwnerUserId AS [Voter_ID], Answers.OwnerUserId AS [Vote_Recipient_ID] INTO #votes_to_audit FROM [Votes] INNER JOIN [Posts] AS Answers ON Votes.PostId = Answers.Id INNER JOIN [Posts] AS Questions ON Answers.ParentId = Questions.Id INNER JOIN [Users] AS Answerer ON Answers.OwnerUserId = Answerer.Id INNER JOIN [Users] AS Questioner ON Questions.OwnerUserId = Questioner.Id WHERE Votes.VoteTypeId = 1 AND Answers.PostTypeId = 2 AND Questions.PostTypeId = 1 AND Answers.CommunityOwnedDate IS NULL AND Questions.OwnerUserId <> Answers.OwnerUserId AND Answerer.Reputation < ##MaxReputation:int?5000## AND Questioner.Reputation < ##MaxReputation:int?5000## AND Votes.CreationDate BETWEEN DATEADD(dd, -##SearchLookupLength:int?10##, ##SearchStartDate?GETDATE()##) AND ##SearchStartDate?GETDATE()##; -- Filter for Users... SELECT CAST(RIGHT(u_audit.User_Filter, LEN(u_audit.User_Filter) - CHARINDEX('-', u_audit.User_Filter)) AS int) AS [User_ID], CAST(SUBSTRING(u_audit.User_Filter, 0, CHARINDEX('-', u_audit.User_Filter)) AS int) AS [Reciever_ID], * INTO #users_to_audit FROM ( SELECT (CASE WHEN audit.Voter_ID > audit.Vote_Recipient_ID THEN CAST(audit.Voter_ID as nvarchar(MAX)) + '-' + CAST(audit.Vote_Recipient_ID as nvarchar(MAX)) ELSE CAST(audit.Vote_Recipient_ID as nvarchar(MAX)) + '-' + CAST(audit.Voter_ID as nvarchar(MAX)) END) AS [User_Filter], SUM(15) AS Reputation_Gained, MAX(audit.Vote_Date) AS Last_Accept FROM #votes_to_audit AS audit GROUP BY (CASE WHEN audit.Voter_ID > audit.Vote_Recipient_ID THEN CAST(audit.Voter_ID as nvarchar(MAX)) + '-' + CAST(audit.Vote_Recipient_ID as nvarchar(MAX)) ELSE CAST(audit.Vote_Recipient_ID as nvarchar(MAX)) + '-' + CAST(audit.Voter_ID as nvarchar(MAX)) END) HAVING COUNT(*) >= ##MinimumAccepts:int?2## ) AS u_audit -- Enrich the Data SELECT user_audit.User_ID, user_audit.Reciever_ID, user_audit.Reputation_Gained, user_audit.Last_Accept, (SELECT COUNT(*) FROM [Votes] AS v INNER JOIN [Posts] AS p1 ON v.PostId = p1.Id INNER JOIN [Posts] AS p2 ON p1.ParentId = p2.Id WHERE p1.OwnerUserId = user_audit.Reciever_ID AND p2.OwnerUserId = user_audit.User_ID AND v.VoteTypeId = 1) AS Total_Accepts_Recieved, (SELECT COUNT(*) FROM [Votes] AS v INNER JOIN [Posts] AS p1 ON v.PostId = p1.Id INNER JOIN [Posts] AS p2 ON p1.ParentId = p2.Id WHERE p1.OwnerUserId = user_audit.User_ID AND p2.OwnerUserId = user_audit.Reciever_ID AND v.VoteTypeId = 1) AS Total_Accepts_Given INTO #enriched_results FROM #users_to_audit AS user_audit -- Result Time SELECT ('https://stackoverflow.com/users/' + CONVERT(nvarchar(25), res.User_ID) + '/?tab=reputation') AS [User 1], ('https://stackoverflow.com/users/' + CONVERT(nvarchar(25), res.Reciever_ID) + '/?tab=reputation') AS [User 2], res.Reputation_Gained AS [Rep within Timeframe], res.Total_Accepts_Recieved AS [Total 1 => 2], res.Total_Accepts_Given AS [Total 2 => 1], res.Last_Accept AS [Last at...] FROM #enriched_results AS res ORDER BY res.Last_Accept DESC, res.User_ID DESC