4

Please login or register to vote for this query.

(click on this box to dismiss)

mod-only version of the original query use in conjunction with SuspiciousVotingHelper userscript (https://github.com/samliew/SO-mod-userscripts) for quick population of the suspicious votes in the CM message

Q&A for anime and manga fans

-- This is a query that attempts to find serial upvoting on Stack Overflow
-- Results returned by this query are fairly accurate but should not be
-- taken as concrete proof that serial upvoting has occured. If you're
-- going to flag based on this query's results, double check every single
-- case beforehand. This is not a tool to be used for witch hunting. Serial
-- Voters are for moderators to deal with - do not use this as a way to
-- publicly air other people's dirty laundry.

-- Version 2.2 (universal site linking)
-- Version 2.2.1 (switched to mod-only reputation link)
-- Version 2.2.2 (add CM message link, use in conjunction with SuspiciousVotingHelper userscript https://github.com/samliew/SO-mod-userscripts)
-- Version 2.2.3 (CM message link includes additional information)


-- MaxReputation: Users above this reputation will be excluded from results
-- DateRange: How far into the past should the query look in days (May cause performance timeouts >800)
-- MinPostCount: How many posts have to have been upvoted in the same timespan to report in this query 
-- MinUpvoteCount: How many upvotes have been gained in the same timspan to report in this query.
-- Sensitivity: How close do the votes have to be together to be counted. More means less restrictive.
-- MinUpvoteCountUnsensitive: How many upvotes have to have been generated if the upvote count doesn't match the upvoted posts count.
-- MinPostCountUnsensitive: How many posts have to have been upvoted if the post count and upvote count doesn't match the upvote count.

SELECT 
vdt.User_ID AS [User Link],
( 'site://admin/show-user-votes/' + CONVERT(nvarchar(20),vdt.User_ID) ) AS [Reputation Link],
u.Reputation AS [User Rep],
CONVERT(VARCHAR(20), vdt.Upvoting_Occured, 107) AS [Date of Vote],
vdt.Upvote_Count AS [Upvotes], 
vdt.Post_Count AS [#Posts],
vdt.Reputation_Gained AS [Reputation+],
( 'site://admin/cm-message/create/' + CONVERT(nvarchar(20),vdt.User_ID) + '?action=suspicious-voting&info=' + REPLACE(CONVERT(VARCHAR(12), vdt.Upvoting_Occured, 107),' ','+') + ':+' + CONVERT(nvarchar(5),vdt.Upvote_Count) + '+upvotes+on+' + CONVERT(nvarchar(5),vdt.Post_Count) + '+posts+that+were+not+previously+auto-reversed+by+the+serial+voting+script' ) AS [Contact CM]

FROM

(
SELECT 
u.Id AS User_Id, 
COUNT(v.Id) AS Upvote_Count,
MAX(v.CreationDate) AS Upvoting_Occured,
COUNT(DISTINCT p.Id) AS Post_Count,
SUM( CASE p.PostTypeId WHEN 1 THEN 5 WHEN 2 THEN 10 ELSE 0 END ) AS Reputation_Gained

FROM Votes AS v
INNER JOIN Posts AS p ON v.PostId = p.Id
INNER JOIN Users AS u ON p.OwnerUserId = u.Id

WHERE ( v.VoteTypeId = 2 ) 
AND ( v.CreationDate != p.CreationDate ) 
AND ( v.CreationDate > DATEADD(dd, -##DateRange:int?28##, GETDATE()) )

GROUP BY 
u.Id,
v.CreationDate,
( v.Id / ##Sensitivity:int?2000## )

) AS vdt

INNER JOIN Users AS u ON vdt.User_Id = u.Id

WHERE ( ( vdt.Upvote_Count >= ##MinUpvoteCountUnsensitive:int?7## ) OR ( vdt.Post_Count = vdt.Upvote_Count ) ) 
AND ( u.Reputation < ##MaxReputation:int?800## ) 
AND ( ( vdt.Post_Count >= ##MinPostCountUnsensitive:int?7## ) OR ( vdt.Post_Count = vdt.Upvote_Count ) )
AND ( vdt.Post_Count >= ##MinPostCount:int?6## )
AND ( vdt.Upvote_Count >= ##MinUpvoteCount:int?6## )
AND ( u.Reputation != 1 )

ORDER BY [Reputation+] DESC

view current version

edited aug 30 20 Samuel Liew

created aug 21 17 Magisch