1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

CREATE TABLE #Editors (
  DbId INT NOT NULL,
  UserId INT NOT NULL,
  Count INT NOT NULL
);

DECLARE @query VARCHAR(MAX) = '';

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

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 ALL
    ';

  SELECT @query = @query + '
    SELECT
      ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId,
      UserId,
      COUNT(*) AS Count
    FROM (
      SELECT Users.AccountId AS UserId, Posts.Id AS PostId
      FROM
        [' + @DbName + '].dbo.PostHistory,
        [' + @DbName + '].dbo.Posts,
        [' + @DbName + '].dbo.Users
      WHERE
        PostHistory.PostId = Posts.Id
        AND
        PostHistory.UserId = Users.Id
        AND
        Users.AccountId > 0
        AND
        Posts.OwnerUserId > 0
        AND
        Posts.OwnerUserId <> PostHistory.UserId
      GROUP BY Users.AccountId, Posts.Id
    ) p
    GROUP BY UserId
  ';

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

INSERT INTO #Editors EXEC (@query);

SELECT TOP 1000
  'https://stackexchange.com/users/' + CAST(UserId AS VARCHAR(MAX))
    + '?tab=accounts|' + CAST(UserId AS VARCHAR(MAX))
    AS [Accounts Link],
  'https://data.stackexchange.com/stackoverflow/query/1089480'
    + '?NetworkAccountId=' + CAST(UserId AS VARCHAR(MAX))
    + '|' + CAST(UserId AS VARCHAR(MAX))
    AS [With Hidden Accounts],
  SUM(Count) AS Count
FROM #Editors
GROUP BY UserId
ORDER BY Count DESC, UserId

fork query

edited apr 23

created apr 23 peterh - PLEASE DELETE THIS ACCOUNT