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

DECLARE @Separator AS CHAR(1) = '|';
DECLARE @TitlePatterns AS NVARCHAR(MAX) = ##TitlePatterns:string?How %|Can %|Should %|Would %|%?##;

CREATE TABLE #Patterns ( Pattern NVARCHAR(256) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL );

-- Split a string into table rows (http://stackoverflow.com/a/2703)
WITH Tokens(p, a, b) AS (
    SELECT 1, 1, CHARINDEX(@Separator, @TitlePatterns)
    UNION ALL
    SELECT p + 1, CAST (b + 1 AS INT), CHARINDEX(@Separator, @TitlePatterns, b + 1)
        FROM Tokens
        WHERE b > 0
)
INSERT INTO #Patterns
    SELECT SUBSTRING(@TitlePatterns, a, CASE WHEN b > 0 THEN b - a ELSE 9999 END) AS Pattern
       FROM Tokens;

WITH InitialRevs AS (
    SELECT PostId
         , Text AS Title
        FROM PostHistory
        WHERE PostHistoryTypeId IN (1) -- Initial Title
), 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]
       , CASE WHEN Q.Title <> InitialRevs.Title THEN InitialRevs.Title END AS [Initial Title (if different)]
       , 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
    WHERE
        Q.PostTypeId IN (1) -- Questions
        AND EXISTS (
            SELECT *
                FROM #Patterns
                WHERE InitialRevs.Title LIKE #Patterns.Pattern
        )
    ORDER BY Q.CreationDate DESC;

fork query

edited mar 30 16

created mar 30 16 200_success