0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those using, extending or developing Emacs

-- Forked from https://data.stackexchange.com/stackoverflow/query/743838/alpha-favoritism-high-user-favorite-correlation
-- to add moderator-only links.
--
-- Detect occurences of low reputation users favoriting excessive amounts of posts from other
-- users, which 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 


FORMATMESSAGE('site://users/%d|%s', u.id, u.DisplayName) AS [Voter],
u.Reputation AS [Voter Reputation],
FORMATMESSAGE('site://admin/show-user-votes/%d|%s votes', u.id, u.DisplayName) AS [Voter Reputation],
FORMATMESSAGE('site://users/%d|%s', u2.id, u2.DisplayName) AS [Recipient],
u2.Reputation AS [Rec Reputation],
FORMATMESSAGE('site://admin/show-user-votes/%d|%s votes', u2.id, u2.DisplayName) AS [Recipient 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?10000## )
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 sep 23 21 Martijn Pieters

created oct 17 17 Magisch