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

Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language

DECLARE @TrustedUserReputation int = 4000
DECLARE @Question tinyint = 1
DECLARE @Answer tinyint = 2
DECLARE @Wiki tinyint = 3
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.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 -- 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.PostTypeId IN (@Question, @Wiki)
                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 -- Locked posts can't be edited
            (
                Posts.PostTypeId IN (@Answer, @Wiki)
                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
                )
            )
            OR Posts.PostTypeId IN (@TagWikiExcerpt, @TagWiki)
        )
    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 '[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[ '']%'
            )
        )
ORDER BY Posts.LastActivityDate DESC

view current version

edited oct 23 17

created nov 6 16 Gao