1

Please login or register to vote for this query.

(click on this box to dismiss)

As decided in http://meta.codereview.stackexchange.com/q/2454, Code Review requires question titles to describe the purpose of the code, rather than the author's concern about the code. As a result, questions that do not follow that convention either need to have their titles revised, or they may be off-topic altogether. This query helps find such problematic questions.

Q&A for peer programmer code reviews

WITH 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
        LEFT OUTER JOIN Locks
            ON Q.id = Locks.PostId
        LEFT OUTER JOIN AnswerCounts
            ON Q.id = AnswerCounts.Id
    WHERE
        Q.PostTypeId IN (1) -- Questions
        AND (
            Q.Title LIKE 'How %' OR
            Q.Title LIKE 'Can %' OR
            Q.Title LIKE 'Should %' OR
            Q.Title LIKE 'Would %' OR
            Q.Title LIKE '%?'
        )
    ORDER BY Q.CreationDate DESC;

view current version

edited mar 30 16

created mar 30 16 200_success