List of users not having a 10k+ account by combined reputation

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @output TABLE (
  DbName VARCHAR(max),
  UserName VARCHAR(max),
  Reputation INT,
  AccountId INT);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE
  (
    name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
    AND
    NOT name 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 AS UserName,
      Reputation,
      AccountId
    FROM [' + @dbname + '].dbo.Users
    WHERE Reputation >= 101;';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH CombinedRep AS (
  SELECT
    MIN(UserName) AS UserName,
    AccountId,
    MAX(Reputation) AS MaxRep,
    SUM(Reputation) AS Reputation
  FROM @output
  GROUP BY AccountId
)
SELECT
  DENSE_RANK() OVER (ORDER BY Reputation DESC) AS Rank,
  'http://stackexchange.com/users/'
    + CAST(AccountId AS VARCHAR(30))
    + '?tab=accounts|' + UserName AS 'SE Account',
  AccountId,
  MaxRep,
  Reputation
FROM CombinedRep
WHERE MaxRep < ##MaximalReputation:INT?10000##
ORDER BY Reputation DESC;

Enter Parameters

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