Sites by percentage of the top-rep accounts (network-wide)

1

Please login or register to vote for this query.

(click on this box to dismiss)

List for all the sites, what percentage of their users have their top-rep account there. It counts only accounts with at least 202 rep.

Physics

Q&A for active researchers, academics and students of physics

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

CREATE TABLE #Sites (
  DbId INT,
  AllAccounts INT,
  MaxCount INT
);

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
  IF @query <> ''
    SET @query = @query + '
    UNION
    ';

  SELECT @query = @query + '
    SELECT
      ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId,
      Users.AccountId AS UserId,
      Users.Reputation AS Rep
    FROM [' + @DbName + '].dbo.Users
    WHERE Reputation >= ' + CAST(##MinRep:int?202## AS VARCHAR(MAX)) + '
  ';

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

CLOSE dbcursor;

PRINT @query;

INSERT INTO #Accounts EXEC (@query);

WITH MaxReps AS (
  SELECT MAX(Rep) AS MaxRep, UserId
  FROM #Accounts
  GROUP BY UserId
)
INSERT INTO #Sites
SELECT
  DbId,
  COUNT(*) AS AllAccounts,
  COUNT(CASE WHEN #Accounts.Rep = MaxReps.MaxRep THEN 1 END) AS MaxCount
FROM #Accounts, MaxReps
WHERE
  #Accounts.UserId = MaxReps.UserId
GROUP BY
  #Accounts.DbId;

SELECT
  sysdatabases.name AS dbname,
  #Sites.AllAccounts,
  #Sites.MaxCount,
  100.0 * #Sites.MaxCount / #Sites.AllAccounts AS PctTop
FROM #Sites, sysdatabases
WHERE
  #Sites.DbId = sysdatabases.dbid
ORDER BY PctTop DESC, #Sites.DbId

Enter Parameters

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