WITH UserCareerPeriods AS ( SELECT u.Id, DATEADD(day, DA...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

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

Enter Parameters

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