Script to identify serial upvoters

4

Please login or register to vote for this query.

(click on this box to dismiss)

Raspberry Pi

Q&A for users and developers of hardware and software for Raspberry Pi

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 (
        SELECT *,
        ROW_NUMBER() OVER(PARTITION BY PostDate ORDER BY Id) AS 'RowNum'
        FROM #tmp
    ) t3
    WHERE t3.RowNum = 1
    GROUP BY t3.Id HAVING COUNT(*) > 10
) t2
ON t1.Id = t2.Id
ORDER BY t1.Id DESC, t1.PostId;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results