0

Please login or register to vote for this query.

(click on this box to dismiss)

Details users' total reputation and the number of SE sites on which they participate across the network. Updated to use AccountId, include Meta.SO and StackApps (and exclude Data.SE).

Q&A for professional and enthusiast programmers

-- User Reputation Across All Sites
-- Details users' total reputation and the number of SE sites on which they participate across the network.

DECLARE @sql nvarchar(MAX)

SELECT
    @sql = COALESCE(@sql + ' UNION ALL ', '') +
        /* Group this to one e-mail hash per site since people have multiple accounts */
        'SELECT
            u.AccountId,
            SUM(u.Reputation) AS Reputation
            FROM ' + QUOTENAME(d.name) + '..Users u
            GROUP BY u.AccountId'
    FROM sys.databases d
    WHERE
        ((d.name NOT LIKE '%Meta%') Or d.name='Stackoverflow.Meta') AND
        (d.name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Data.StackExchange'))

SET @sql = '
    SELECT
        d.MinRep,
        d.MaxRep,
        e.MinSites,
        e.MaxSites,
        SUM(Users) AS Users
        FROM
        (
            SELECT
                c.MinRep,
                c.MaxRep,
                b.SiteCount,
                COUNT(1) AS Users
                FROM
                (
                    SELECT
                        COUNT(1) AS SiteCount,
                        SUM(a.Reputation) AS TotalReputation
                        FROM
                        (' + @sql + ') a
                        GROUP BY a.AccountId
                ) b
                INNER JOIN
                (
                    SELECT 1 AS MinRep, 100 AS MaxRep
                    UNION ALL
                    SELECT 101, 499
                    UNION ALL
                    SELECT 500, 999
                    UNION ALL
                    SELECT 1000, 1999
                    UNION ALL
                    SELECT 2000, 2999
                    UNION ALL
                    SELECT 3000, 4999
                    UNION ALL
                    SELECT 5000, 9999
                    UNION ALL
                    SELECT 10000, 99999999
                ) c ON
                    (b.TotalReputation >= c.MinRep) AND
                    (b.TotalReputation <= c.MaxRep)
                GROUP BY
                    c.MinRep,
                    c.MaxRep,
                    b.SiteCount
        ) d
        INNER JOIN
        (
            SELECT 1 AS MinSites, 1 AS MaxSites
            UNION ALL
            SELECT 2, 2
            UNION ALL
            SELECT 3, 3
            UNION ALL
            SELECT 4, 4
            UNION ALL
            SELECT 5, 5
            UNION ALL
            SELECT 6, 9
            UNION ALL
            SELECT 10, 14
            UNION ALL
            SELECT 15, 19
            UNION ALL
            SELECT 20, 999
        ) e ON
            (d.SiteCount >= e.MinSites) AND
            (d.SiteCount <= e.MaxSites)
        GROUP BY
            d.MinRep,
            d.MaxRep,
            e.MinSites,
            e.MaxSites
            WITH ROLLUP
        HAVING
            NOT ((e.MinSites IS NOT NULL) AND (e.MaxSites IS NULL)) AND
            NOT ((d.MinRep IS NOT NULL) AND (d.MaxRep IS NULL))
        ORDER BY
            GROUPING(d.MinRep),
            d.MinRep,
            GROUPING(e.MinSites),
            e.MinSites
'

EXEC(@sql)

fork query

edited feb 16 14 Mark Hurd

created jul 17 11 Jon Seigel