Sites by relative meta activity (compared to the main)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Compares the SE sites to their metas - shows the percentage of the users having a post on the meta site, compared to the percentage of the users having a post on the main. Counts only the users over the RepLimit. You can set this RepLimit to 1.

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

DECLARE dbcursor CURSOR FOR
WITH Raw AS (
  SELECT name
  FROM sysdatabases
  WHERE name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
)
SELECT Main.name AS MainDb, Meta.name AS MetaDb
FROM Raw Main, Raw Meta
WHERE
  Main.name + '.Meta' = Meta.name;

DECLARE @MainDb VARCHAR(MAX);
DECLARE @MetaDb VARCHAR(MAX);
DECLARE @Query VARCHAR(MAX);
DECLARE @Output TABLE (
  MainDb VARCHAR(MAX),
  MainUserCount INT,
  MetaDb VARCHAR(MAX),
  MetaUserCount INT
);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @MainDb, @MetaDb;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @Query = '
    SELECT
      ''' + @MainDb + ''' AS MainDb,
      (SELECT COUNT(DISTINCT OwnerUserId)
      FROM [' + @MainDb + '].dbo.Posts
      LEFT JOIN [' + @MainDb + '].dbo.Users
        ON Posts.OwnerUserId = Users.Id
        WHERE Users.Reputation >= 
          ' + CAST(##RepLimit:int?301## AS VARCHAR(MAX)) + ') AS MainUserCount,
      ''' + @MetaDb + ''' AS MetaDb,
      (SELECT COUNT(DISTINCT OwnerUserId)
      FROM [' + @MetaDb + '].dbo.Posts
      LEFT JOIN [' + @MetaDb + '].dbo.Users
        ON Posts.OwnerUserId = Users.Id
      WHERE Users.Reputation >=
        ' + CAST(##RepLimit## AS VARCHAR(MAX)) + ') AS MetaUserCount
  ';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @MainDb, @MetaDb;
END;

SELECT
  *,
  100.0 * MetaUserCount / MainUserCount AS MetaActivityPct
FROM @output
ORDER BY MetaActivityPct DESC;

Enter Parameters

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