Q&A for professional and enthusiast programmers
WITH UserCareerPeriods AS ( SELECT u.Id, DATEADD(day, DATEDIFF(day, u.CreationDate, GETDATE())/2, u.CreationDate) AS MidPoint FROM Users AS u WHERE u.Reputation > 101 ), UserCareer AS ( SELECT ucp.Id, MidPoint, SUM(CASE WHEN p.PostTypeId = 1 AND p.CreationDate < ucp.MidPoint THEN 1 ELSE 0 END) AS QuestionsPart1, SUM(CASE WHEN p.PostTypeId = 1 AND p.CreationDate >= ucp.MidPoint THEN 1 ELSE 0 END) AS QuestionsPart2, SUM(CASE WHEN p.PostTypeId = 2 AND p.CreationDate < ucp.MidPoint THEN 1 ELSE 0 END) AS AnswersPart1, SUM(CASE WHEN p.PostTypeId = 2 AND p.CreationDate >= ucp.MidPoint THEN 1 ELSE 0 END) AS AnswersPart2 FROM UserCareerPeriods AS ucp JOIN Posts p ON p.OwnerUserId = ucp.Id GROUP BY ucp.Id, ucp.MidPoint ), Transitions AS ( SELECT Id, CASE WHEN QuestionsPart1 > AnswersPart1 THEN 'Asker' WHEN QuestionsPart1 = AnswersPart1 THEN 'Undecided' ELSE 'Answerer' END AS Part1, CASE WHEN QuestionsPart2 > AnswersPart2 THEN 'Asker' WHEN QuestionsPart2 = AnswersPart2 THEN 'Undecided' ELSE 'Answerer' END AS Part2 FROM UserCareer where AnswersPart2 > 0 or QuestionsPart2 >0 ) SELECT [Part1], [Asker] AS [Part2Asker], [Answerer] AS [Part2Answerer], [Undecided] AS [Part2Undecided] FROM (SELECT COUNT(Id) AS Nr, Part1, Part2 FROM Transitions GROUP BY Part1, Part2) AS MyPivot PIVOT (SUM(Nr) FOR Part2 IN ([Asker], [Answerer], [Undecided])) AS PvtTable