Get Multi-Account users who upvote themself

0

Please login or register to vote for this query.

(click on this box to dismiss)

Computational Science

Q&A for scientists using computers to solve scientific problems

-- Get Multi-Account users who upvote themself

select Email.UserID as [User Link]
,v.PostID as [Post Link]
,Email.Accounts
,Email.Reputation
,Email.LastAccessDate
,Row_Number()Over(Partition By p.ID Order By v.ID)as NumPostDelicts
,Row_Number()Over(Partition By Email.UserID Order By p.ID)as NumUserDelicts
from(
SELECT
    Row_Number()Over(PARTITION BY EmailHash order by u1.reputation desc)as Rank
    ,u1.ID AS UserID
    ,u1.Reputation
    ,u1.EmailHash
    ,u1.LastAccessDate
    ,(Select count(*) from users u2 where u2.emailhash=u1.emailhash)AS Accounts
FROM
    Users u1
WHERE
    u1.EmailHash IS NOT NULL
)email INNER JOIN Users u ON u.ID=Email.UserID
INNER JOIN Posts p ON p.OwnerUserId=u.ID
INNER JOIN Votes v ON p.ID=v.PostID
where Accounts>1 AND Rank=1
AND p.OwnerUserId=email.UserID AND v.UserID IN(SELECT u3.ID FROM users u3 where u3.emailhash=email.emailhash)
ORDER BY
   NumUserDelicts DESC, NumPostDelicts DESC, Accounts DESC, Reputation DESC, LastAccessDate DESC

Enter Parameters

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