0

Please login or register to vote for this query.

(click on this box to dismiss)

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

view current version

edited jul 22

created jul 22 Magisch