3

Please login or register to vote for this query.

(click on this box to dismiss)

Posts with moderation notices that have not been cleared. Optionally enter a UserId to include only notices created by a specific user.

Q&A for those who study, teach, research and apply economics and econometrics

DECLARE @UserId INT = ##UserId?0##;

WITH PostNotices AS (
    SELECT PostId
         , MIN(CreationDate) AS [Notice Date]
        FROM PostHistory
        WHERE PostHistoryTypeId IN (14, 33, 34)
            -- Locked/Post notice added/Post notice removed, resp.
        GROUP BY PostId
        HAVING
            COUNT(CASE WHEN PostHistoryTypeId = 33 THEN 1 END)
            - COUNT(CASE WHEN PostHistoryTypeId = 34 THEN 1 END)
            - COUNT(CASE WHEN PostHistoryTypeId = 14 THEN 1 END)
            > 0
), RecentPostNotices AS (
    SELECT PostId
         , UserId
         , CreationDate
         , RANK() OVER (PARTITION BY PostId ORDER BY CreationDate DESC) AS NMostRecent
        FROM PostHistory
        WHERE PostHistoryTypeId IN (33) -- Post notice added
), BountyStarts AS (
    SELECT PostId
         , CreationDate
        FROM Votes
        WHERE VoteTypeId IN (8) -- BountyStart
), PostEdits AS (
    SELECT PostId
         , MAX(CreationDate) AS [Edit Date]
        FROM PostHistory
        WHERE PostHistoryTypeId IN (5) -- Edit body
        GROUP BY PostId
), PostDeletions AS (
    SELECT PostId
         , MAX(CreationDate) AS [Deletion Date]
        FROM PostHistory
        WHERE PostHistoryTypeId IN (12, 13) -- Post notice deleted/undeleted, resp.
        GROUP BY PostId
        HAVING
            COUNT(CASE WHEN PostHistoryTypeId = 12 THEN 1 END)
            - COUNT(CASE WHEN PostHistoryTypeId = 13 THEN 1 END)
            > 0
)
-- Notes:
--
-- Locking a question creates a post notice.  To exclude locking, we'll
-- count Lock events as if they were Post Notice Removed events.
--
-- Starting a bounty also creates a post notice.  Those aren't the
-- kinds of notices we are interested in, so we exclude those using an
-- anti-join.  The CreationDate for bounty starts only has DATE granularity,
-- not DATETIME.
SELECT PostNotices.PostId AS [Post Link]
     , DisplayName AS [Post Notice By]
     , [Notice Date]
     , [Edit Date]
     , [Deletion Date]
    FROM PostNotices
        INNER JOIN RecentPostNotices
            ON PostNotices.PostId = RecentPostNotices.PostId
            AND NMostRecent = 1
        LEFT OUTER JOIN BountyStarts
            ON RecentPostNotices.PostId = BountyStarts.PostId
            AND CAST(RecentPostNotices.CreationDate AS DATE) = BountyStarts.CreationDate
        INNER JOIN Users
            ON RecentPostNotices.UserId = Users.Id
        LEFT OUTER JOIN PostEdits
            ON PostNotices.PostId = PostEdits.PostId
            AND [Notice Date] < [Edit Date]
        LEFT OUTER JOIN PostDeletions
            ON PostNotices.PostId = PostDeletions.PostId
    WHERE @UserId IN (RecentPostNotices.UserId, 0)
        AND BountyStarts.PostId IS NULL
    ORDER BY [Notice Date];

fork query

edited jun 23 14

created jun 23 14 200_success