Network-wide stats for user account creation after post migration

2

Please login or register to vote for this query.

(click on this box to dismiss)

Android Enthusiasts

Q&A for enthusiasts and power users of the Android operating system

/* Result columns (accounts are on target sites):

  TotalMigrated: Total count of posts migrated.
  HaveAccount: Migrated posts where OP currently has an account.
  HadAccount: Migrated posts where OP had an account already before migration.
  AccountAfterPost: Where OP created an account after the post/migration was made.
  AccountAfterMigrate: Where OP created an account after the migration was made.
  NoAccount: Where OP never created an account.
  NoAccountOpen: Where OP never created an account, and question is still open.
  NoAccountPostActivity: Where OP never created an account, and question got a
    comment or an answer after the migration.
  NoAccountPostActivityOpen: Where OP never created an account, and question is
    still open and got a comment or an answer after the migration.

 */

DECLARE @db sysname
DECLARE @query nvarchar(max)
DECLARE c CURSOR FOR 
  SELECT Name 
  FROM sys.databases 
  WHERE database_id > 5

CREATE TABLE #Results (Site sysname,
                       TotalMigrated int,
                       HaveAccount int,
                       HadAccount int,
                       AccountAfterPost int,
                       AccountAfterMigrate int,
                       NoAccount int,
                       NoAccountOpen int,
                       NoAccountPostActivity int,
                       NoAccountPostActivityOpen int)

OPEN c
FETCH NEXT FROM c INTO @db
WHILE (@@FETCH_STATUS = 0) BEGIN
  SET @query =
    '
    USE ' + QUOTENAME(@db) + ';
    WITH Activity AS (
      SELECT DISTINCT
        h.PostId
      FROM
        PostHistory h
        INNER JOIN (
          SELECT PostId, CreationDate FROM Comments
          UNION ALL SELECT ParentId PostId, CreationDate FROM Posts
        ) a ON a.PostId = h.PostId
      WHERE
        h.PostHistoryTypeId = 36 
        AND a.CreationDate >= h.CreationDate
    )
    INSERT INTO #Results SELECT
      DB_NAME() Site,
      COUNT(*) TotalMigrated,
      COUNT(p.OwnerUserId) HaveAccount,
      COALESCE(SUM(IIF(u.CreationDate < p.CreationDate, 1, 0)), 0) HadAccount,
      COALESCE(SUM(IIF(u.CreationDate > p.CreationDate, 1, 0)), 0) AccountAfterPost,
      COALESCE(SUM(IIF(u.CreationDate > h.CreationDate, 1, 0)), 0) AccountAfterMigrate,
      COALESCE(SUM(IIF(p.OwnerUserId IS NULL, 1, 0)), 0) NoAccount,
      COALESCE(SUM(IIF(p.OwnerUserId IS NULL AND p.ClosedDate IS NULL, 1, 0)), 0) NoAccount,
      COALESCE(SUM(IIF(p.OwnerUserId IS NULL AND a.PostId IS NOT NULL, 1, 0)), 0) NoAccountPostActivity,
      COALESCE(SUM(IIF(p.OwnerUserId IS NULL AND p.ClosedDate IS NULL AND a.PostId IS NOT NULL, 1, 0)), 0) NoAccountPostActivityOpen
    FROM
      PostHistory h
      INNER JOIN Posts p ON p.Id = h.PostId
      LEFT OUTER JOIN Users u ON u.Id = p.OwnerUserId
      LEFT OUTER JOIN Activity a ON a.PostId = h.PostId
    WHERE
      h.PostHistoryTypeId = 36
      AND p.PostTypeId = 1
    '
  EXEC(@Query)
  FETCH NEXT FROM c INTO @db
END;
CLOSE c;
DEALLOCATE c; 

SELECT * FROM #Results
UNION ALL SELECT
  'TOTAL',
  SUM(TotalMigrated),
  SUM(HaveAccount),
  SUM(HadAccount),
  SUM(AccountAfterPost),
  SUM(AccountAfterMigrate),
  SUM(NoAccount),
  SUM(NoAccountOpen),
  SUM(NoAccountPostActivity),
  SUM(NoAccountPostActivityOpen)
FROM #Results
ORDER BY TotalMigrated DESC, NoAccount DESC, Site ASC

Enter Parameters

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