4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

-- On Recommendation of Brad Larson this is my first attempt to detect occurences
-- of low reputation users favoriting excessive amounts of posts from other low
-- reputation users, which according to Brad is a decent-ish predictor of people
-- in voting rings. 

-- MaxVoterReputation: Voters above this reputation will not be considered
-- MaxRecipientReputation: Recipients above this reputation will not be considered
-- MinFavorites: How many favorites of a specific users does a user need to have to show up
-- DateRange: How long ago does the last vote need to be to be considered
-- VoteDateRange: Only votes younger then this will be considered (in days)

SELECT 


('site://users/' + CAST(fcv.voter_id AS NVARCHAR) + '|'+ u.DisplayName ) AS [Voter],
u.Reputation AS [Voter Reputation],
('site://users/' + CAST(fcv.recipient_id AS NVARCHAR) + '|'+ u2.DisplayName ) AS [Recipient],
u2.Reputation AS [Rec Reputation],
fcv.no_favorite_votes AS [Favorite Votes],
CONVERT(VARCHAR(20), fcv.latest_vote, 107) AS [Last Favorite]


FROM
(

SELECT
u.Id AS voter_id,
COUNT(v.Id) AS no_favorite_votes,
u2.Id AS recipient_id,
MAX(v.CreationDate) AS latest_vote


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

WHERE ( u.Id <> u2.Id )
AND ( u.Reputation <= ##MaxVoterReputation:int?2500## )
AND ( u2.Reputation <= ##MaxRecipientReputation:int?3000## )
AND ( v.CreationDate > DATEADD(dd, -##VoteDateRange:int?300##, GETDATE()) )


GROUP BY
u.Id,
u2.Id

HAVING COUNT(v.Id) > ##MinFavorites:int?5##
AND ( MAX(v.CreationDate) > DATEADD(dd, -##DateRange:int?300##, GETDATE()) )

) AS fcv

INNER JOIN Users AS u ON fcv.voter_id = u.Id
INNER JOIN Users AS u2 ON fcv.recipient_id = u2.Id

ORDER BY 
fcv.latest_vote DESC,
fcv.no_favorite_votes

fork query

edited oct 17 17

created oct 17 17 Magisch