Posts that probably need editing

5

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

Mythology Meta

Q&A about the site for enthusiasts and scholars of mythology and folklore

SELECT DISTINCT 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 > 0 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 IN (1, 3) AND
              Posts.Id <> ALL (
                  SELECT DISTINCT PostId
                  FROM PostHistory
                  WHERE PostId = PostHistory.PostId AND
                        PostHistoryTypeId IN (14, 15)
                  GROUP BY PostId
                  HAVING COUNT(PostHistoryTypeId) % 2 = 1
              )
          ) OR -- Locked posts can't be edited
          (
              Posts.PostTypeId IN (2, 3) AND
              Posts.ParentId <> ALL (
                  SELECT DISTINCT PostId
                  FROM PostHistory
                  WHERE PostId = Posts.ParentId AND
                        PostHistoryTypeId IN (14, 15)
                  GROUP BY PostId
                  HAVING COUNT(PostHistoryTypeId) % 2 = 1
              )
          ) OR
          Posts.PostTypeId IN (4, 5)
      ) AND
      (
          (
              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.Title LIKE '[abcdefghijklmnopqrstuvwxyz]%' OR
              Posts.Title LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]([ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrtuvwxyz]%' OR
              Posts.Title LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrtuvwxyz])[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]%' OR
              Posts.Title LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz],[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]%' OR
              Posts.Title LIKE '% i[ '']%'
          ) OR
          (
              LOWER(Posts.Title) LIKE '%' + REPLACE(REPLACE(REPLACE(Posts.Tags, '-', ' '), '<', ' '), '>', ' ') + '%'
          ) OR -- Potentially redundant titles that include the tag names (must include all tags in order to keep simplicity in implementation)
          (
              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[ '']%'
          )
      ) AND
      (
          Posts.ClosedDate IS NULL OR
          DATEDIFF(day, Posts.ClosedDate, GETDATE()) BETWEEN 6 AND 90
      ) -- Don't edit recently closed questions as doing so will bump them into the review queue
ORDER BY Posts.LastActivityDate DESC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results