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

Q&A for professionals and students of engineering

DECLARE @TrustedUserReputation int = 20000;
DECLARE @Question tinyint = 1;
DECLARE @Answer tinyint = 2;
DECLARE @TagWikiExcerpt tinyint = 4;
DECLARE @TagWiki tinyint = 5;
DECLARE @PostLocked tinyint = 14;
DECLARE @PostUnlocked tinyint = 15;

SELECT DISTINCT
      PostTypes.Name AS [Post Type]
    , Posts.Score AS [Post Score]
    , Posts.Id AS [Post Link]
    , Posts.OwnerUserId AS [User Link]
    , Posts.LastActivityDate AS [Last Activity]
    , Posts.Tags
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 -- Low quality posts are probably not worth salvaging
    AND Posts.ClosedDate IS NULL
    AND Users.Reputation < @TrustedUserReputation -- Posts by trusted users are exempt from editing (that is the theory anyway)
    AND (
            (
                Posts.Title NOT LIKE '%?'
                AND (
                       LOWER(Posts.Title) LIKE 'wh[aeoy]%'
                    OR LOWER(Posts.Title) LIKE 'how %'
                    OR LOWER(Posts.Title) LIKE 'should%'
                    OR LOWER(Posts.Title) LIKE 'can%'
                    OR LOWER(Posts.Title) LIKE 'is[n ]%'
                    OR LOWER(Posts.Title) LIKE 'are[n ]%'
                    OR LOWER(Posts.Title) LIKE 'do[en ]%'
                )
            )
            OR
            (
                   Posts.Title LIKE '[a-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Title LIKE '%[A-Za-z][(),][A-Za-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Title LIKE '% i[ '']%'
            )
            OR
            (
                   Posts.Body LIKE '[a-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Body LIKE '%[A-Za-z][(),][A-Za-z]%' COLLATE Latin1_General_BIN2
                OR Posts.Body LIKE '% i[ '']%'
            )
        )
    AND (
            (
                Posts.PostTypeId = @Question
                AND Posts.Id <> ALL (
                    SELECT DISTINCT PostId
                    FROM PostHistory
                    WHERE
                        PostId = PostHistory.PostId
                        AND PostHistoryTypeId IN (@PostLocked, @PostUnlocked)
                    GROUP BY PostId
                        HAVING COUNT(PostHistoryTypeId) % 2 = 1
                )
            )
            OR
            (
                Posts.PostTypeId = @Answer
                AND Posts.ParentId <> ALL (
                    SELECT DISTINCT PostId
                    FROM PostHistory
                    WHERE
                        PostId = Posts.ParentId
                        AND PostHistoryTypeId IN (@PostLocked, @PostUnlocked)
                    GROUP BY PostId
                        HAVING COUNT(PostHistoryTypeId) % 2 = 1
                )
            ) -- Locked posts can't be edited
            OR Posts.PostTypeId IN (@TagWikiExcerpt, @TagWiki)
        )
ORDER BY Posts.LastActivityDate DESC;

fork query

edited jul 4 20

created nov 6 16 Gao