comment partition by question


Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

--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
            AS Question
            -- take only those posts that are in CS
		FROM        CS 
			ON Posts.Id = CS.PostId
        -- 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 [Comment Link] from PS
        GROUP BY        Question

Enter Parameters

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