Site dominators, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

List of the SE accounts having the largest percentage of the total site reputation

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

CREATE TABLE #Accounts (
  DbId INT,
  UserId INT,
  Rep INT
);

CREATE TABLE #Sites (
  DbId INT,
  TotalRep BIGINT
);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT dbid, 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);
DECLARE @DbId INT;

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DbId, @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT
      ''' + CAST(@DbId AS VARCHAR(MAX)) + ''' AS DbId,
      Users.AccountId AS UserId,
      Users.Reputation AS Rep
    FROM [' + @DbName + '].dbo.Users
    WHERE
      Reputation >= 202
  ';

  INSERT INTO #Accounts EXEC (@query);

  SELECT @query = '
    SELECT
      ''' + CAST(@DbId AS VARCHAR(MAX)) + ''' AS DbId,
      SUM(Users.Reputation) AS TotalRep
    FROM [' + @DbName + '].dbo.Users
  ';

  INSERT INTO #Sites EXEC(@query);

  FETCH NEXT FROM dbcursor INTO @DbId, @DbName;
END

CLOSE dbcursor;

SELECT TOP 100
  #Accounts.UserId, sysdatabases.name, #Accounts.Rep, #Sites.TotalRep,
  100.0 * #Accounts.Rep / #Sites.TotalRep AS Pct
FROM #Accounts, #Sites, sysdatabases
WHERE
  #Accounts.DbId = #Sites.DbId
  AND
  #Sites.DbId = sysdatabases.dbid
ORDER BY Pct DESC, #Accounts.UserId
--ORDER BY 100.0 * #Accounts.Rep / #Sites.TotalRep DESC, #Accounts.UserId

Enter Parameters

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