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;