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)

Monero Meta

Q&A about the site for developers and users of the secure, private and untraceable cryptocurrency Monero

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