1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people seeking specific hardware recommendations

CREATE TABLE #results (site NVARCHAR(250),
                       accountid INT,
                       displayname NVARCHAR(40),
                       numberofposts INT);

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + IIF(LEN(@sql) > 0, 'UNION ALL', 'INSERT INTO #results') +
N'
SELECT ''' + name + ''', u.AccountId, u.DisplayName, COUNT(*)
  FROM ' + QUOTENAME(name) + '.dbo.Posts AS p  
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Users AS u
    ON p.OwnerUserId = u.Id
  WHERE p.PostTypeId = ' + CAST(##PostTypeId:int?1## AS NVARCHAR) + '
  GROUP BY u.AccountId, u.DisplayName
'
-- PostTypeId: Post type ID: 1 = Questions, 2 = Answers
FROM sys.databases WHERE database_id > 5
  AND name != 'StackOverflow' -- needs special treatment

EXEC (@sql);

DELETE FROM #results WHERE accountid IN (
SELECT accountid
  FROM #results
  GROUP BY accountid
  HAVING SUM(numberofposts) < ##MinimumNumberOfPosts:int?1000## / 2);
  
INSERT INTO #results
SELECT 'StackOverflow', u.AccountId, u.DisplayName, COUNT(*)
  FROM [StackOverflow].dbo.Posts AS p  
  INNER JOIN [StackOverflow].dbo.Users AS u
    ON p.OwnerUserId = u.Id
  WHERE p.PostTypeId = ##PostTypeId:int?1##
  GROUP BY u.AccountId, u.DisplayName
  HAVING COUNT(*) >= ##MinimumNumberOfPosts:int?1000## / 2
UNION
SELECT 'StackOverflow', u.AccountId, u.DisplayName, COUNT(*)
  FROM [StackOverflow].dbo.Posts AS p
  INNER JOIN [StackOverflow].dbo.Users AS u
    ON p.OwnerUserId = u.Id
  WHERE p.PostTypeId = ##PostTypeId:int?1##
    AND u.AccountId IN (SELECT DISTINCT(accountid) FROM #results)
  GROUP BY u.AccountId, u.DisplayName;

SELECT CONCAT('https://stackexchange.com/users/', accountid,
              '?tab=accounts|', MAX(displayname)) AS 'User',
  SUM(numberofposts) AS '# of posts'
  FROM #results
  GROUP BY accountid
  HAVING SUM(numberofposts) >= ##MinimumNumberOfPosts:int?1000##
  ORDER BY SUM(numberofposts) DESC;
     
DROP TABLE #results;

fork query

edited dec 24

created dec 24 Glorfindel