Posts that probably need editing

4

Please login or register to vote for this query.

(click on this box to dismiss)

Updated with feedback from CodeReview.SE: https://codereview.stackexchange.com/q/176680/54092

Physics

Q&A for active researchers, academics and students of physics

SELECT PostTypes.Name AS [Post Type],
                Posts.Id AS [Post Link],
                Posts.LastActivityDate AS [Last Activity]
FROM Posts
INNER JOIN PostTypes
ON Posts.PostTypeId = PostTypes.Id
INNER JOIN Users
ON Posts.OwnerUserId = Users.Id
INNER JOIN PostHistory
ON Posts.Id = PostHistory.PostId
WHERE 
Posts.Score > -1 AND -- Low quality posts are probably not worth salvaging
Users.Reputation < 4000 AND -- Posts by trusted users are exempt from editing (that is the theory anyway)
(
    (
        Posts.PostTypeId = 1 AND
        Posts.Id <> (
            SELECT TOP 1 PostId
            FROM PostHistory
            WHERE PostHistory.PostId = PostId AND
                  PostHistoryTypeId IN (14, 15)
            GROUP BY PostId
            HAVING COUNT(PostHistoryTypeId) % 2 = 1
        )
    ) OR -- Locked posts can't be edited
    (
        Posts.PostTypeId = 2 AND
        Posts.ParentId <> (
            SELECT TOP 1 PostId
            FROM PostHistory
            WHERE PostHistory.PostId = PostId AND
                  PostHistoryTypeId IN (14, 15)
            GROUP BY PostId
            HAVING COUNT(PostHistoryTypeId) % 2 = 1
        )
    ) OR
    Posts.PostTypeId IN (4, 5)
) AND
(
    Posts.Title LIKE '[abcdefghijklmnopqrstuvwxyz]%' OR
    (
        Posts.Title NOT LIKE '%?' AND
        (
            LOWER(Posts.Title) LIKE 'is %' OR
            LOWER(Posts.Title) LIKE 'are %' OR
            LOWER(Posts.Title) LIKE 'do[en ]%' OR
            LOWER(Posts.Title) LIKE '%wh[aeoy]%' OR
            LOWER(Posts.Title) LIKE '%how%'
        )
    ) OR
    (
        (Posts.ClosedDate IS NULL OR DATEDIFF(day, Posts.ClosedDate, GETDATE()) > 6) AND -- Don't edit recently closed questions as doing so will bump them into the review queue
        (
            Posts.Body LIKE '[abcdefghijklmnopqrstuvwxyz]%' OR
            Posts.Body LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]([ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrtuvwxyz]%' OR
            Posts.Body LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrtuvwxyz])[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]%' OR
            Posts.Body LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz],[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]%' OR
            Posts.Body LIKE '% i[ '']%'
        )
    )
)
ORDER BY Posts.LastActivityDate ASC

Enter Parameters

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