Roomba-eligible questions sans comment requirement

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 Baseline AS (SELECT DISTINCT COUNT(*) AS Eligible
                  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.CommentCount <= 1
                        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

),
    AddedCommentProviso AS (SELECT DISTINCT COUNT(*) AS EligibleWithoutComments
                            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
  Eligible,
  EligibleWithoutComments,
  (EligibleWithoutComments - Eligible) AS Delta
FROM Baseline, AddedCommentProviso;

Enter Parameters

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