2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

CREATE TABLE #output (
  dbname VARCHAR(max),
  UserName NVARCHAR(40),
  SEAccountId INT,
  HnqCount INT
);

DECLARE @query VARCHAR(MAX);

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

DECLARE @dbname VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT
      ''' + @dbname + ''' AS dbname,
      MIN(Users.DisplayName) AS UserName,
      Users.AccountId AS SEAccountId,
      COUNT(*) AS HnqCount
    FROM
      [' + @dbname + '].dbo.Users,
      [' + @dbname + '].dbo.Posts,
      [' + @dbname + '].dbo.PostHistory
    WHERE
      PostHistory.PostHistoryTypeId = 52
      AND
      PostHistory.PostId = Posts.Id
      AND
      Posts.OwnerUserId = Users.Id
    GROUP BY Users.AccountId
    ';
  INSERT INTO #output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH Raw AS (
  SELECT
    SUM(HnqCount) AS HnqCount,
    MIN(UserName) AS UserName,
    SEAccountId
  FROM #output
  GROUP BY SEAccountId
)
SELECT
  DENSE_RANK() OVER (ORDER BY HnqCount DESC) AS Rank,
  UserName,
  SEAccountId,
  HnqCount,
  'http://stackexchange.com/users/'
    + CAST(SEAccountId AS VARCHAR(30))
    + '?tab=accounts|' + UserName AS 'SE Profile',
  'https://data.stackexchange.com/stackoverflow/query/678540?AccountId='
    + CAST(SEAccountId AS VARCHAR(30)) + '#resultSets|' + UserName
    AS [With Hidden Accounts]
FROM Raw
ORDER BY HnqCount DESC, SEAccountId;

fork query

edited aug 14 19

created apr 15 19 peterh - PLEASE DELETE THIS ACCOUNT