Users with most self-answered questions, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

DECLARE @output TABLE (
  DbName VARCHAR(MAX),
  AccountId INT,
  UserName VARCHAR(MAX),
  No 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,
      Users.AccountId,
      MIN(Users.DisplayName) AS UserName,
      COUNT(*) AS No
    FROM
      [' + @dbname + '].dbo.Posts Q,
      [' + @dbname + '].dbo.Posts A,
      [' + @dbname + '].dbo.Users
    WHERE
      Q.OwnerUserId = Users.Id
      AND
      A.ParentId = Q.Id
      AND
      A.OwnerUserId = Q.OwnerUserId
    GROUP BY Users.AccountId';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

SELECT AccountId,
  'https://stackexchange.com/users/' + CAST(AccountId AS VARCHAR(MAX))
    + '|' + MIN(UserName) AS [User],
  SUM(No) AS No
FROM @output
WHERE AccountId > -1
GROUP BY AccountId
ORDER BY No DESC

Enter Parameters

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