Roomba-eligible questions comment breakdown

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @query_date DATE = GETDATE() - 90;

SELECT DISTINCT
  COUNT(*) AS ImpactedQuestions,
  p.CommentCount
FROM PostsWithDeleted p
WHERE p.PostTypeId = 1
      AND p.Score = CASE WHEN p.OwnerUserId IS NULL
  THEN 1
                    ELSE 0 END
      AND p.CreationDate < DATEADD(DAY, -365, @query_date)
      AND p.AnswerCount = 0
      AND p.ViewCount <= DATEDIFF(DAY, p.CreationDate, @query_date) * 1.5
      AND p.DeletionDate IS NULL
      AND COALESCE((SELECT TOP 1 ph.PostHistoryTypeId
                    FROM PostHistory ph
                      INNER JOIN PostHistoryTypes pht ON ph.PostHistoryTypeId = pht.Id
                    WHERE ph.PostId = p.Id
                          AND pht.Name IN ('Post Locked', 'Post Unlocked')
                    ORDER BY ph.Id DESC), 0) <> 14
GROUP BY p.CommentCount
ORDER BY p.CommentCount ASC;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results