1

Please login or register to vote for this query.

(click on this box to dismiss)

Summarizes users' total reputation and the number of SE sites on which they participate across the network.

Q&A for pro webmasters

-- Stack Exchange Network Participation
-- Summarizes 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.EmailHash,
            SUM(u.Reputation) AS Reputation
            FROM ' + QUOTENAME(d.name) + '..Users u
            GROUP BY u.EmailHash'
    FROM sys.databases d
    WHERE
        (d.name NOT LIKE '%Meta%') AND
        (d.name NOT IN ('master', 'model', 'tempdb', 'msdb', 'StackApps.Exported'))

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.EmailHash
                ) 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, 24999
                    UNION ALL
                    SELECT 25000, 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

created jul 17 11 Jon Seigel