0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for practitioners, researchers, and students in cognitive science, psychology, neuroscience, and psychiatry

--select posts, referred in comments
		  WITH CS AS (
		  SELECT      PostId, count(Id) as CommentedPosts, max(Id) as SampleComment
			FROM        Comments
			WHERE       UserId = ##UserId## 
			GROUP BY        PostId
		)
        
        -- The posts in CS are either answers or questions.
        -- In case of answers, translate them into the question.
        , PS as (
		SELECT CS.PostId, CommentedPosts, SampleComment,
        
            -- translate postId into questionId
            CASE    WHEN Posts.PostTypeId = 1
                    THEN Posts.Id
                    ELSE Posts.ParentId
            END
            AS Question
            
            -- take only those posts that are in CS
		FROM        CS 
			INNER JOIN Posts
			ON Posts.Id = CS.PostId
        ), Questions as (
        
        
        
        -- Merge the comments by question
        select Question, Question as [Post Link] --, count(PostId) as CommentedPosts, max(PostId) as SamplePost
        , count(CommentedPosts) as CommentedPosts, max(SampleComment) as SampleComment 
        from PS
        GROUP BY        Question
        )
        
        select * from questions

fork query

edited sep 22 13 val

created sep 22 13 anonymous