0

Please login or register to vote for this query.

(click on this box to dismiss)

Duplicated emailhashes with some reputation, probably worth getting merged if validly the same person.

Q&A for professional and enthusiast programmers

SELECT 
    -- u1.EmailHash,
    Count(u1.Id) AS Accounts,
    Sum(u1.Reputation) As [Total Rep],
    Sum(Case When u1.Reputation > 400 Then 1 Else 0 End) As [Some Rep],
    (SELECT u2.DisplayName + ':' + Cast(u2.Id AS varchar) + ' (' + Cast(u2.Reputation AS varchar) + '), ' FROM Users u2 WHERE u2.AccountId = u1.AccountId Order By u2.Reputation DESC FOR XML PATH ('')) AS NamesIdsAndRep
FROM
    Users u1
WHERE
    u1.AccountId IS NOT NULL
GROUP BY
    u1.AccountId
HAVING
    Count(u1.Id) > 1 AND Sum(Case When u1.Reputation > 400 Then 1 Else 0 End)> 1
ORDER BY 
 Sum(u1.Reputation)-(SELECT Max(u2.Reputation) FROM Users u2 WHERE u2.AccountId = u1.AccountId) DESC, Accounts DESC

fork query

edited jun 1 15 Mark Hurd

created may 15 10 anonymous