Q&A for science fiction and fantasy enthusiasts
;WITH headless AS ( -- how many questions migrated from SO -- still don't have a valid owner? SELECT QuestionCount = COUNT(*) FROM dbo.Posts AS p INNER JOIN dbo.PostHistory AS ph ON p.Id = ph.PostID WHERE ph.PostHistoryTypeId = 36 AND p.OwnerUserId IS NULL AND p.PostTypeId = 1 AND ph.Comment LIKE 'from http://stackoverflow%' ), newusers AS ( -- users who created their account after -- their question was migrated from SO SELECT u.Id, u.CreationDate FROM dbo.Posts AS p INNER JOIN dbo.PostHistory AS ph ON p.Id = ph.PostID INNER JOIN dbo.Users AS u ON p.OwnerUserId = u.Id WHERE ph.PostHistoryTypeId = 36 AND p.PostTypeId = 1 AND ph.Comment LIKE 'from http://stackoverflow%' AND u.CreationDate > p.CreationDate ), laterposts AS ( -- of those "new users", how many -- questions/answers did they post -- *after* their account was migrated? SELECT UserId = nu.Id, LaterQuestions = SUM(CASE WHEN PostTypeId = 1 THEN 1 ELSE 0 END), LaterAnswers = SUM(CASE WHEN PostTypeId = 2 THEN 1 ELSE 0 END) FROM newusers AS nu INNER JOIN dbo.Posts AS p ON nu.Id = p.OwnerUserId AND p.CreationDate > nu.CreationDate GROUP BY nu.Id ), latercounts AS ( -- just a summary row for the above SELECT UserId = 'All new users', QuestionCount = SUM(LaterQuestions), AnswerCount = SUM(LaterAnswers) FROM laterposts ) SELECT UserId, QuestionCount, AnswerCount FROM (SELECT UserId = 'Headless Questions', QuestionCount, AnswerCount = NULL FROM headless UNION ALL SELECT RTRIM(UserId), LaterQuestions, LaterAnswers FROM laterposts UNION ALL SELECT UserId, QuestionCount, AnswerCount FROM latercounts ) AS x ORDER BY CASE WHEN ISNUMERIC(UserId)=0 THEN 1 ELSE 2 END, QuestionCount DESC, AnswerCount DESC;