What new users (spurred by migration) went on to do...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio 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;

Enter Parameters

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