Users with the highest combined reputation, network-wide

2

Please login or register to vote for this query.

(click on this box to dismiss)

It counts only the >=101 accounts.

Stack Overflow

Q&A for professional and enthusiast programmers

-- @Gilles is simply... extreme

CREATE TABLE #output (
  dbname VARCHAR(max),
  DisplayName NVARCHAR(40),
  Reputation INT,
  SEAccountId INT
);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE (
    name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
    AND
    name NOT LIKE '%.Meta'
  )
  OR
  name = 'StackExchange.Meta';

DECLARE @dbname VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT ''' + @dbname + ''' AS dbname,
      DisplayName,
      Reputation,
      AccountId AS SEAccountId
    FROM [' + @dbname + '].dbo.Users
    WHERE Reputation >= 101;';
  INSERT INTO #output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH Raw AS (
  SELECT
    COUNT(*) AS N,
    SUM(Reputation) AS CombinedRep,
    MIN(DisplayName) AS DisplayName,
    SEAccountId
  FROM #output
  GROUP BY SEAccountId
)
SELECT
  DENSE_RANK() OVER (ORDER BY CombinedRep DESC) AS Rank,
  N AS 'Number of Accounts',
  CombinedRep,
  'http://stackexchange.com/users/'
    + CAST(SEAccountId AS VARCHAR(30))
    + '?tab=accounts|' + DisplayName AS [User]
FROM Raw
ORDER BY CombinedRep DESC, SEAccountId;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results