Quality control for questions with certain phrases in the body


Please login or register to vote for this query.

(click on this box to dismiss)

Code Review

Q&A for peer programmer code reviews

WITH InitialRevs AS (
    SELECT PostId
         , Text AS Body
        FROM PostHistory
        WHERE PostHistoryTypeId IN (2) -- Initial Body
), Locks AS (
    SELECT PostId
         , LAST_VALUE(PostHistoryTypeId) OVER (PARTITION BY PostId ORDER BY CreationDate) AS PostHistoryTypeId
        FROM PostHistory
        WHERE PostHistoryTypeId IN (14, 15) -- (Locked, Unlocked)
), AnswerCounts AS (
    SELECT ParentId AS Id
         , COUNT(*) AS AnswerCount
        FROM Posts
        WHERE PostTypeId IN (2) -- Answers
            AND DeletionDate IS NULL
        GROUP BY ParentId
) SELECT Q.Id AS [Post Link]
       , Q.Score
       , CASE WHEN Q.ClosedDate IS NOT NULL THEN N'❌' END AS Closed
       , CASE WHEN Locks.PostHistoryTypeId = 14 THEN N'🔐' END AS Locked
       , Q.CreationDate
       , COALESCE(AnswerCounts.AnswerCount, 0) AS Answers
    FROM Posts AS Q
        INNER JOIN InitialRevs
            ON Q.id = InitialRevs.PostId
        LEFT OUTER JOIN Locks
            ON Q.id = Locks.PostId
        LEFT OUTER JOIN AnswerCounts
            ON Q.id = AnswerCounts.Id
        Q.PostTypeId IN (1) -- Questions
        AND InitialRevs.Body LIKE CONCAT('%', ##BodySearchString:string##, '%')
    ORDER BY Q.CreationDate DESC;

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results