User Activity Shifts Quarterly

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT COUNT(User_Id_Q1)Users
  ,Engagment_Q1
  , ISNULL(Engagment_Q2,'Disengaged')Engagment_Q2
FROM

--Firstquarter
(SELECT ISNULL(OwnerUserID,UserID)User_Id_Q1
      , Question AS Question_Q1
      ,Answer AS Answer_Q1
      ,Comment AS Comment_Q1
      ,CASE WHEN Comment>0 and Answer>0 and Question>0 THEN 'High Engaged'
            WHEN Answer>0 THEN 'Responder'
            WHEN Question>0 THEN 'Questor'
            WHEN Comment>0 THEN 'Commentor' ELSE 'Other' END Engagment_Q1
FROM 
(SELECT OwnerUserID
  ,SUM(CASE WHEN PostTypeID=1 THEN 1 ELSE 0 END) As Question
  ,SUM(CASE WHEN PostTypeID=2 THEN 1 ELSE 0 END) As Answer

FROM Posts
WHERE Year(CreationDate)=2015 and Month(CreationDate) in (1,2,3)
GROUP BY OwnerUserID)a

FULL OUTER JOIN

(SELECT UserId, COUNT(*)Comment

FROM Comments
WHERE Year(CreationDate)=2015 and Month(CreationDate) in (1,2,3)
GROUP BY UserID)b

ON a.OwnerUserID = b.UserId

)Q1

LEFT JOIN

--Secound quarter
(SELECT ISNULL(OwnerUserID,UserID)User_Id_Q2    
      , Question AS Question_Q2
      ,Answer AS Answer_Q2
      ,Comment AS Comment_Q2
      ,CASE WHEN Comment>0 and Answer>0 and Question>0 THEN 'High Engaged'
            WHEN Answer>0 THEN 'Responder'
            WHEN Question>0 THEN 'Questor'
            WHEN Comment>0 THEN 'Commentor' ELSE 'Other' END Engagment_Q2

FROM 
(SELECT OwnerUserID
  ,SUM(CASE WHEN PostTypeID=1 THEN 1 ELSE 0 END) As Question
  ,SUM(CASE WHEN PostTypeID=2 THEN 1 ELSE 0 END) As Answer

FROM Posts
WHERE Year(CreationDate)=2015 and Month(CreationDate) in (4,5,6)
GROUP BY OwnerUserID)a

FULL OUTER JOIN

(SELECT UserId, COUNT(*)Comment

FROM Comments
WHERE Year(CreationDate)=2015 and Month(CreationDate) in (4,5,6)
GROUP BY UserID)b

ON a.OwnerUserID = b.UserId

)Q2

ON Q1.User_ID_Q1 = Q2.User_ID_Q2

GROUP BY Engagment_Q1, Engagment_Q2

Enter Parameters

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