Script to identify serial upvoters

4

Please login or register to vote for this query.

(click on this box to dismiss)

Economics

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

SELECT Users.Id, Users.Reputation, Users.DisplayName, 
       Badges.Date As BadgeDate,
       Posts.Id AS PostId, Posts.CreationDate AS PostDate,
       Votes.VoteTypeId AS VoteId, Votes.CreationDate AS VoteDate
INTO #tmp
FROM Users
     LEFT JOIN Badges ON Users.Id = Badges.UserId
     LEFT JOIN Posts ON Users.Id = Posts.OwnerUserId
     LEFT JOIN Votes ON Posts.Id = Votes.PostId
WHERE Badges.Name='Mortarboard' 
  AND Votes.VoteTypeId=2
  AND CAST(Badges.Date AS DATE)=Votes.CreationDate
  AND CAST(Posts.CreationDate AS DATE)!=Votes.CreationDate;

SELECT *,
    ROW_NUMBER() OVER(PARTITION BY PostDate ORDER BY Id) AS 'RowNum'
INTO #tmpunique
FROM #tmp


SELECT * FROM #tmp t1

INNER JOIN (
    SELECT t3.Id
    FROM #tmpunique t3
    GROUP BY t3.Id HAVING COUNT(*) > 10
) t2
ON t1.Id = t2.Id

ORDER BY t1.Id DESC, t1.PostId;






--GROUP BY Users.Id HAVING COUNT(*) > 15;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results