Serial Upvote Detection Script


Please login or register to vote for this query.

(click on this box to dismiss)

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

-- Courtesy: [Magisch](
-- MaxReputation: Max reputation to show up in 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.
-- MinReputationGained: How much Reputation does the incident have to be worth to be considered.

vdt.User_ID AS [User Link],
('' + CONVERT(nvarchar(25), vdt.User_ID) + '/?tab=reputation') AS [Reputation Link],
--( 'site://users/' + CONVERT(nvarchar(20),vdt.User_ID) + '/' + REPLACE(u.DisplayName, ' ', '-') + '?tab=reputation' ) AS [Reputation Link],
u.Reputation AS [User Rep],
CONVERT(VARCHAR(20), vdt.Upvoting_Occured, 107) AS [Date of Vote],
vdt.Upvote_Count AS [Upvote Count], 
vdt.Post_Count AS [Number of Posts],
vdt.Reputation_Gained AS [Reputation+],
u.DisplayName AS [User Name]


u.Id AS User_Id, 
COUNT(v.Id) AS Upvote_Count,
MAX(v.CreationDate) AS Upvoting_Occured,
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?10##, GETDATE()) )

( v.Id / ##Sensitivity:int?2500## )

) 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?5000## ) 
AND ( ( vdt.Post_Count >= ##MinPostCountUnsensitive:int?7## ) OR ( vdt.Post_Count = vdt.Upvote_Count ) )
AND ( vdt.Post_Count >= ##MinPostCount:int?5## )
AND ( vdt.Upvote_Count >= ##MinUpvoteCount:int?5## )
AND ( u.Reputation != 1 ) -- This is to exclude suspended users with otherwise high rep
AND ( vdt.Reputation_Gained >= ##MinReputationGained:int?35## )

ORDER BY vdt.Upvoting_Occured DESC,
vdt.User_ID DESC

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results