Sites by relative meta activity (compared to the main) (parallelizable version)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Constructed Languages

Q&A for designers and users of artificial or invented languages, consciously developed instead of having developed naturally

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange');

DECLARE @DbName VARCHAR(MAX);
DECLARE @Query VARCHAR(MAX) = '';
DECLARE @Output TABLE (
  Db VARCHAR(MAX),
  UserCount INT
);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @Query <> '' SELECT @Query = @Query + ' UNION ';
    
  SELECT @Query = @Query + '
    SELECT
      ''' + @DbName + ''' AS Db,
      COUNT(DISTINCT OwnerUserId)
    FROM [' + @DbName + '].dbo.Posts
    LEFT JOIN [' + @DbName + '].dbo.Users
      ON Posts.OwnerUserId = Users.Id
    WHERE Users.Reputation >= 
      ' + CAST(##RepLimit:int?301## AS VARCHAR(MAX)) + '
  ';
  FETCH NEXT FROM dbcursor INTO @DbName;
END;

INSERT INTO @output EXEC (@Query);

SELECT
  Main.Db AS MainDb,
  Main.UserCount AS MainUserCount,
  Meta.Db AS MetaDb,
  Meta.UserCount AS MetaUserCount,
  100.0 * Meta.UserCount / Main.UserCount AS MetaActivityPct
FROM @output Main, @output Meta
WHERE
  Main.Db + '.Meta' = Meta.Db
ORDER BY MetaActivityPct;

Enter Parameters

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