3

Please login or register to vote for this query.

(click on this box to dismiss)

First Attempt at finding Serial Downvoting

Q&A about the site for Drupal developers and administrators

-- Simple query to identify people getting serially downvoted

-- Version 2.0 ( Added Parameters to make search functionality configurable )

-- DateRange: How long should the query look back in days? High amounts can cause timeout.
-- MinPosts: How many posts at the minimum have to have been downvoted?
-- Sensitivity: How close do the downvotes have to have been to each other? More = Less restrictive
-- MinDownvoteCount: How many downvotes does a post have to have recieved to be considered
-- MaxDownvoteCount: How many downvotes can a post recieve before being no longer considered

SELECT 
vdv.USER_ID AS [User Link],
( 'site://users/' + CONVERT(nvarchar(20),vdv.USER_ID) + '/' + REPLACE(MAX(u.DisplayName), ' ', '-') + '?tab=reputation' ) AS [Reputation Link],
CONVERT(VARCHAR(20), vdv.VOTE_DATE, 107) AS [Date of Occurence],
vdv.VOTE_COUNT AS [Votes per Post],
COUNT(vdv.POST_ID) AS [Posts Downvoted]

FROM
(

--- Posts that have recieved more then 2 votes a day 
SELECT 
COUNT(v.Id) AS VOTE_COUNT,
p.Id AS POST_ID,
p.OwnerUserId AS USER_ID,
v.CreationDate AS VOTE_DATE,
MAX(v.Id) AS MAX_VOTE_ID

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

WHERE ( v.CreationDate > DATEADD(dd, -##DateRange:int?800##, GETDATE()) )
AND ( v.VoteTypeId = 3 )

GROUP BY p.Id, 
v.CreationDate, 
p.OwnerUserId

HAVING ( COUNT(v.Id) >= ##MinDownvoteCount:int?1## ) 
AND ( COUNT(v.Id) < ##MaxDownvoteCount:int?8## )

) AS vdv

INNER JOIN Users AS u ON vdv.USER_ID = u.Id

GROUP BY 
vdv.USER_ID,
vdv.VOTE_DATE,
vdv.VOTE_COUNT,
( vdv.MAX_VOTE_ID / ##Sensitivity:int?400## )

HAVING COUNT(vdv.POST_ID) >= ##MinPosts:int?3##

ORDER BY vdv.VOTE_DATE DESC

fork query

edited oct 16 17

created aug 10 17 Magisch