Roomba-eligible questions action 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;

WITH ImpactedQuestions AS (SELECT DISTINCT p.Id
                           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)
SELECT
  pht.Name,
  COUNT(*)
FROM ImpactedQuestions iq LEFT JOIN PostHistory ph ON iq.Id = ph.PostId
  LEFT JOIN PostHistoryTypes pht ON ph.PostHistoryTypeId = pht.Id
GROUP BY ph.PostHistoryTypeId, pht.Name
ORDER BY COUNT(*) DESC

Enter Parameters

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