Comment Distribution

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

WITH ThreadCommentCount AS (
  SELECT
    MIN(p.CreationDate) AS ThreadDate,
    ISNULL(p.ParentId, p.Id) TopPostId,
    COUNT(DISTINCT c.Id) AS CommentCount
  FROM Posts p
  LEFT JOIN Comments c on c.PostId = p.Id
  where p.PostTypeId IN (1,2) -- questions and answers
  GROUP BY ISNULL(p.ParentId, p.Id)
--  ORDER BY 1 ASC
)
  
SELECT
  SUM(CASE WHEN CommentCount >= 0 and CommentCount < 5 THEN 1 ELSE 0 END) AS "0-5",
  SUM(CASE WHEN CommentCount >= 5 and CommentCount < 10 THEN 1 ELSE 0 END) AS "5-10",
  SUM(CASE WHEN CommentCount >= 10 and CommentCount < 15 THEN 1 ELSE 0 END) AS "10-15",
  SUM(CASE WHEN CommentCount >= 15 and CommentCount < 20 THEN 1 ELSE 0 END) AS "15-20",
  SUM(CASE WHEN CommentCount >= 20 and CommentCount < 25 THEN 1 ELSE 0 END) AS "20-25",
  SUM(CASE WHEN CommentCount >= 25 and CommentCount < 30 THEN 1 ELSE 0 END) AS "25-30",
  SUM(CASE WHEN CommentCount >= 30 and CommentCount < 35 THEN 1 ELSE 0 END) AS "30-35",
  SUM(CASE WHEN CommentCount >= 35 and CommentCount < 40 THEN 1 ELSE 0 END) AS "35-40",
  SUM(CASE WHEN CommentCount >= 40 and CommentCount < 45 THEN 1 ELSE 0 END) AS "40-45",
  SUM(CASE WHEN CommentCount >= 45 and CommentCount < 50 THEN 1 ELSE 0 END) AS "45-50",
  SUM(CASE WHEN CommentCount >= 50 THEN 1 ELSE 0 END) AS "50+"
FROM ThreadCommentCount
WHERE ThreadDate > '2013-07-01'

Enter Parameters

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