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

Stack Overflow

Q&A for professional and enthusiast programmers

--- 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 to meta site
loading Hold tight while we fetch your results