Users wrote most pct of posts in a site

1

Please login or register to vote for this query.

(click on this box to dismiss)

Emacs

Q&A for those using, extending or developing Emacs

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

DECLARE @DbName VARCHAR(MAX);
DECLARE @DbId VARCHAR(MAX);
DECLARE @Query VARCHAR(MAX) = '';
CREATE TABLE #Output (
  DbId INT,
  UserId INT,
  Count INT,
  PRIMARY KEY (UserId, DbId),
  INDEX idx (DbId, UserId)
);

/*
DECLARE @SiteQuery VARCHAR(MAX) = '';

DECLARE @Sites TABLE (
  DbId INT,
  DbName VARCHAR(MAX),
  Count INT
);*/

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

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @Query <> '' SELECT @Query = @Query + ' UNION ';
    
  SELECT @Query = @Query + '
    SELECT
      ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId,
      Users.AccountId,
      COUNT(*) AS Count
    FROM
      [' + @DbName + '].dbo.Posts,
      [' + @DbName + '].dbo.Users
    WHERE
      Posts.OwnerUserId = Users.Id
      AND
      Posts.PostTypeId BETWEEN 1 AND 2
      AND
      Users.Reputation >= 
        ' + CAST(##RepLimit:int?201## AS VARCHAR(MAX)) + '
    GROUP BY Users.AccountId
  ';
  FETCH NEXT FROM dbcursor INTO @DbId, @DbName;
END;

INSERT INTO #output EXEC (@Query);

WITH Raw AS (
  SELECT DbId, SUM(Count) AS Count
  FROM #output
  GROUP BY DbId
)
SELECT TOP 100
  #output.UserId,
  #output.DbId,
  MIN(sysdatabases.name) AS DbName,
  100.0 * SUM(#output.Count) / MIN(Raw.Count) AS Pct
FROM #output, Raw, sysdatabases
WHERE
  #output.DbId = Raw.DbId
  AND
  sysdatabases.DbId = Raw.DbId
GROUP BY #output.DbId, #output.UserId
ORDER BY Pct DESC, UserId

/*
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 to meta site
loading Hold tight while we fetch your results