0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for professionals in medical and allied health fields, students of those professions, related academics, and others with a sound understanding of medicine and healthcare-related sciences

DECLARE @X INT = 4773612;

WITH ACTIVITY_LOG AS (
  SELECT U.ID USER_ID,P.ID ACTIVITY_ID, YEAR(P.CREATIONDATE) CREATION_DATE, 'POST' ACTIVITY_TYPE
    FROM USERS U
    INNER JOIN POSTS P
    ON U.ID=P.OWNERUSERID
    WHERE U.ID=@X
    AND P.CREATIONDATE >= U.CREATIONDATE

  UNION ALL

  SELECT U.ID USER_ID,C.ID ACTIVITY_ID, YEAR(C.CREATIONDATE) CREATION_DATE, 'COMMENT' ACTIVITY_TYPE
    FROM USERS U
    INNER JOIN COMMENTS C
    ON U.ID=C.USERID
    WHERE U.ID=@X
    AND C.CREATIONDATE >= U.CREATIONDATE

  UNION ALL

  SELECT U.ID USER_ID,V.ID ACTIVITY_ID, YEAR(V.CREATIONDATE) CREATION_DATE, 'VOTE' ACTIVITY_TYPE
    FROM USERS U
    INNER JOIN VOTES V
    ON U.ID=V.USERID
    WHERE U.ID=@X
    AND V.CREATIONDATE >= U.CREATIONDATE

  UNION ALL

  SELECT U.ID USER_ID,SEV.ID ACTIVITY_ID, YEAR(SEV.CREATIONDATE) CREATION_DATE, 'SUGGESTEDEDITVOTES' ACTIVITY_TYPE
    FROM USERS U
    INNER JOIN SUGGESTEDEDITVOTES SEV
    ON U.ID=SEV.USERID
    WHERE U.ID=@X
    AND SEV.CREATIONDATE >= U.CREATIONDATE
)
--SELECT * FROM ACTIVITY_LOG
,YEAR_WISE_ACTIVITY AS (
  SELECT CREATION_DATE, COUNT(1) ACTIVITY_COUNT
  FROM ACTIVITY_LOG
  GROUP BY CREATION_DATE
)
SELECT * FROM YEAR_WISE_ACTIVITY;

view current version

edited aug 9 22

created aug 9 22 anonymous