0

Please login or register to vote for this query.

(click on this box to dismiss)

A list of users that have duplicate accounts on site, based on the EmailHash and lots of reputation is riding on it

Q&A for professional and enthusiast programmers

-- Reputation of users with more than 20 duplicate accounts
-- A list of users that have duplicate accounts on site, based on the EmailHash
-- Based on Sam's http://data.stackexchange.com/stackoverflow/query/975/users-with-more-than-one-duplicate-account-and-a-more-than-1000-reputation-in-agg

select 
    u1.EmailHash,
    count(u1.Id) as Accounts,
    (
        select cast(u2.Id as varchar) + ' (' + u2.DisplayName + ' ' + cast(u2.reputation as varchar) + '), ' 
        from Users u2 
        where u2.EmailHash = u1.EmailHash
        order by u2.Reputation desc FOR XML PATH ('')
    ) as IdsAndNames,
    (
        select sum(u3.Reputation) from Users u3 
        where u3.EmailHash = u1.EmailHash
    ) as TotalRep
from
    Users u1
where
    u1.EmailHash is not null
    and (select count(*) from Users u4 where u4.EmailHash = u1.EmailHash) > 20
group by
    u1.EmailHash
having
    Count(u1.Id) > 1
order by
    TotalRep desc

fork query

edited may 21 12 Arjan

created may 25 10 sam.saffron