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 * FROM #tmp t1

INNER JOIN (
    SELECT t3.Id
    FROM #tmp t3
    inner join (
      select distinct PostId, min(Id) as Id
      from #tmp 
      group by PostId
    ) t4
    on t3.PostId = t4.PostId 
    and t3.Id = t4.Id
    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