Your activity history by month, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

DECLARE @SEAccountId INT;

IF ##SiteAccountId:string?-## = '-'
  SET @SEAccountId = CAST(##NetworkAccountId:string?-## AS INT)
ELSE
  SELECT @SEAccountId = AccountId
  FROM Users
  WHERE Id = CAST(##SiteAccountId## AS INT);

DECLARE @SEAccountStr VARCHAR(MAX) = CAST(@SEAccountId AS VARCHAR(MAX));

DECLARE @output TABLE (
  DbName VARCHAR(MAX),
  Month DATETIME,
  Type VARCHAR(MAX),
  Count 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
  IF @query <> ''
    SET @query = @query + '
    UNION ALL
    ';

  PRINT @query;

  SET @query = @query + '
    SELECT
      ''' + @dbname + ''' AS DbName,
      DATEFROMPARTS(
        YEAR(Posts.CreationDate), MONTH(Posts.CreationDate), 1) AS Month,
      CASE WHEN PostTypeId = 1 THEN ''Question'' ELSE ''Answer'' END
        AS Type,
      COUNT(*) AS Count
    FROM [' + @dbname + '].dbo.Users, [' + @dbname + '].dbo.Posts
    WHERE
      Posts.OwnerUserId = Users.Id
      AND
      Posts.PostTypeId BETWEEN 1 AND 2
      AND
      Users.AccountId = ' + @SEAccountStr + '
    GROUP BY
      Posts.PostTypeId,
      DATEFROMPARTS(YEAR(Posts.CreationDate), MONTH(Posts.CreationDate), 1)
    
    UNION ALL

    SELECT
      ''' + @dbname + ''' AS DbName,
      DATEFROMPARTS(
        YEAR(Comments.CreationDate),
        MONTH(Comments.CreationDate),
        1) AS Month,
      ''Comments'' Type,
      COUNT(*) AS Count
    FROM [' + @dbname + '].dbo.Users, [' + @dbname + '].dbo.Comments
    WHERE
      Comments.UserId = Users.Id
      AND
      Users.AccountId = ' + @SEAccountStr + '
    GROUP BY
      DATEFROMPARTS(
        YEAR(Comments.CreationDate), MONTH(Comments.CreationDate), 1)
  ';

-- SEDE is slow and ms sql is s..t to handle this
/*
    UNION ALL

    SELECT
      ''' + @dbname + ''' AS DbName,
      DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS Month,
      ''Edits'' Type,
      COUNT(*) AS Count
    FROM (
      SELECT
        PostHistory.RevisionGUID,
        MIN(PostHistory.CreationDate) AS CreationDate
      FROM [' + @dbname + '].dbo.Users, [' + @dbname + '].dbo.PostHistory
      WHERE
        PostHistory.UserId = Users.Id
        AND
        PostHistory.PostHistoryTypeId <= 9
        AND
        Users.AccountId = ' + @SEAccountStr + '
      GROUP BY
        PostHistory.RevisionGUID
    ) p
    GROUP BY DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)
*/

  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

PRINT @query;

INSERT INTO @output EXEC (@query);

SELECT
  Month,
  Type,
  SUM(Count) AS Count
FROM @output
GROUP BY Month, Type
ORDER BY Month DESC, Type;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results