Top Network Users By Combined Site Reputation

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- This query produces a top 100 list of users by combined network 
-- reputation across all sites, considering only sites where the user
-- has > 200 rep as per combined flair rules.
--
-- Enter your account ID in MyAccountID to see your rank as well.
--
-- I have no idea why it is so fast.

DECLARE @db SYSNAME
DECLARE @q NVARCHAR(max)
DECLARE c CURSOR FOR 
  SELECT Name 
  FROM sys.databases 
  WHERE database_id > 5
  AND (name NOT LIKE '%.Meta' OR name = 'StackExchange.Meta')
CREATE TABLE #users (AccountId int, DisplayName NVARCHAR(40), Reputation int);

OPEN c
FETCH NEXT FROM c INTO @db
WHILE (@@FETCH_STATUS = 0) BEGIN
  SET @q = 'USE ' + QUOTENAME(@db) + ';'
    + 'INSERT INTO #users '
    + 'SELECT AccountId, DisplayName, Reputation '
    + 'FROM Users '
    + 'WHERE Reputation > 200'
  EXEC(@q)
  FETCH NEXT FROM c INTO @db
END;
CLOSE c;
DEALLOCATE c;

SELECT 
  *
FROM (
  SELECT 
    RANK() OVER (ORDER BY SUM(Reputation) DESC) Rank,
    MAX(DisplayName) PossibleDisplayName,
    SUM(Reputation) CombinedReputation, 
    COUNT(*) SiteCount,
    'https://stackexchange.com/users/' + LTRIM(RTRIM(STR(AccountId))) Profile, 
    AccountId
  FROM #users 
  GROUP BY AccountId) x
WHERE Rank <= 100 OR AccountId = ##MyAccountId##
ORDER BY CombinedReputation DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results