Roomba-eligible questions signpost impact

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 COUNT(*) FROM ImpactedQuestions iq
LEFT JOIN PostHistory ph ON ph.PostId = iq.Id
WHERE ph.PostHistoryTypeId = 10 AND ph.Comment IN (1, 101);

Enter Parameters

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