Posts by Stack Exchange staff (network wide)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Parenting

Q&A for parents, grandparents, nannies and others with a parenting role

-- A list of user IDs can be obtained from the API, with the script here:
-- https://meta.stackexchange.com/a/331141/295232
DECLARE @UserIDs NVARCHAR(MAX) =
'3,4,51,91,5190,6212,23354,50049,130213,131257,139284,135201,141235,141525,146126,155160,156631,164200,165581,173432,175506,187267,193284,200773,202841,208517,208518,213659,213916,220750,222882,233415,235711,238712,238801,238921,242868,242956,248860,253117,253547,254686,259107,261314,262550,265649,265754,267421,267572,269744,274323,275592,278972,280328,282241,284336,285849,286963,287938,288412,289568,292464,292509,296470,296784,306119,306180,306181,308650,308664,309381,309382,309868,310174,310571,311079,312625,312743,312922,314762,314875,318071,318945,318951,318952,323037,325611,327819,328394,330582,335054,340078,340281,340282,342225,342786,344092,344828,348754,349208,349251,350924,352085,354392,355163,355481,358207,362644,364360,368280,369250,370877,371894,371930,371932,371941,373679,376728,379210,379213,380111,383293,384372,384467,386446,386975,386976,386977,387521,389304,389943,390399,390411,390413,392414,392464,393819,394111,394114,395497,398530,399529,400423,401062,401063,401322,401460,402517,403834,409959,409960,409964,410743,423506,430118,432335,440650,452001,453303,453304,453464,460639,485283,485365,493532,497552,498329,498833,502975,507720,508266,510771,512317,512881,516441,519992,519993,520046,523443,528105,531877,531878,534929,545299,558563,564417,564418,566698,566699,566700,576323,576461,579126,584503,589597,590098,593716,595342,597236,605701,605716,605965,605969,616040,616085,617305,652456,661133,661134,666372,666888,669812,682777,704608,713084,713085,713087,713088,713089,713090,713091,713092,713093,713094,713427,726273,728969,730623,736305,737401,737413,738349,748542,754060,766682,769510,775525,784098,784336,784338,784339,814785,789076,807466,807495,807496,807497,816947,821203,825530'
;

CREATE TABLE #Accounts (AccountId INT, DisplayName NVARCHAR(40));
INSERT INTO #Accounts
SELECT AccountId, DisplayName
  FROM [StackExchange.Meta].dbo.Users
  WHERE Id IN (SELECT VALUE FROM STRING_SPLIT(@UserIDs, ','));
  
CREATE TABLE #results (Site NVARCHAR(50),
  PostId INT, Title NVARCHAR(250), Score INT, CreationDate DATETIME, Type NVARCHAR(20),
  UserId INT, DisplayName NVARCHAR(40), Reputation INT);

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'
SELECT ''' + name + ''', p.Id, COALESCE(p.Title, q.Title), p.Score, p.CreationDate,
  CASE p.PostTypeId WHEN 1 THEN ''Question'' WHEN 2 THEN ''Answer'' END,
  u.Id, u.DisplayName, u.Reputation
  FROM ' + QUOTENAME(name) + '.dbo.Posts AS p
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Users AS u
    ON p.OwnerUserId = u.Id
  INNER JOIN #Accounts AS a
    ON a.AccountId = u.AccountId
  LEFT OUTER JOIN ' + QUOTENAME(name) + '.dbo.Posts AS q
    ON p.ParentId = q.Id
  WHERE p.CreationDate BETWEEN ''' + ##fromDate:string## + '''
                           AND ''' + ##tillDate:string## + '''
'
FROM sys.databases
WHERE database_id > 5
  AND name NOT LIKE '%.Meta'

EXEC (@sql)

SELECT Site,
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      ))
  + '/q/' + CAST(postid AS NVARCHAR) + '|' + COALESCE(title, '[LINK]') AS 'link',
  Type, Score, CreationDate,
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      )) + '/u/' + CAST(userid AS NVARCHAR) + '|' + COALESCE(displayname, '[USER]') AS 'Author', Reputation
  FROM #results
  ORDER BY Score DESC;
     
DROP TABLE #results

Enter Parameters

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