Get users with multiple accounts

0

Please login or register to vote for this query.

(click on this box to dismiss)

Ordered by Account-Number and Reputation

Computational Science

Q&A for scientists using computers to solve scientific problems

--- Get users with multiple accounts
--- ordered my Account-Number and reputation
select id as [User Link]
,Accounts
,Reputation
,LastAccessDate
from(
SELECT
    Row_Number()Over(PARTITION BY EmailHash order by u1.reputation desc)as Rank
    ,u1.ID
    ,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
where Accounts>5 AND Rank=1
ORDER BY
   Accounts DESC, Reputation DESC, LastAccessDate DESC

Enter Parameters

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