1

Please login or register to vote for this query.

(click on this box to dismiss)

Migrations, merges and meta sites affect the rules a little bit, this query does not deal with them. Used [this](https://meta.stackexchange.com/help/roomba) roomba doc to create the query.

Q&A for linguists, teachers and students of the Korean language

DECLARE @now DATETIME = (SELECT MAX(CreationDate) FROM Posts);
/*
DECLARE @UserId INT = (
  CASE
    WHEN ##SiteAccountId:int?0## = 0 THEN ##SiteAccountId##
    ELSE (SELECT Id FROM Users WHERE AccountId = ##NetworkAccountId:int?0##)
  END
);
*/

WITH Locked AS (
  SELECT PostId
  FROM PostHistory
  WHERE
    Id IN (
      SELECT MAX(Id)
      FROM PostHistory
      WHERE PostHistoryTypeId BETWEEN 14 AND 15
      GROUP BY PostId
    )
    AND
    PostHistoryTypeId = 14
),
Migrated AS (
  SELECT PostId
  FROM PostHistory
  WHERE PostHistoryTypeId BETWEEN 35 AND 36
  GROUP BY PostId
),
QMin2C AS (
  SELECT Posts.Id
  FROM Posts, Comments
  WHERE
    Comments.PostId = Posts.Id
    AND
    Posts.PostTypeId = 2
  GROUP BY Posts.Id
  HAVING COUNT(*) > 1
),
Raw AS (
  SELECT
    Id,
    DATEADD(d, 9,
      CASE
        WHEN LastEditDate > ClosedDate THEN LastEditDate ELSE ClosedDate
      END) AS DeletionDate,
    'RemoveAbandonedClosed' AS Message
  FROM Posts
  WHERE
    PostTypeId = 1
    AND
    ClosedDate < DATEADD(d, -9, @now)
    AND
    Score <= 0
    AND
    AcceptedAnswerId IS NULL
    AND (
      LastEditDate IS NULL
      OR
      LastEditDate < DATEADD(d, -9, @now)
    )
    AND
    Id NOT IN (SELECT PostId FROM PostLinks WHERE LinkTypeId = 3)
    AND
    Id NOT IN (
      SELECT Q.Id
      FROM Posts Q, Posts A
      WHERE
        A.ParentId = Q.Id
        AND
        Q.ClosedDate IS NOT NULL
        AND
        A.Score > 0
    )

  UNION

  SELECT
    Id,
    DATEADD(d, 30, CreationDate) AS DeletionDate,
    'RemoveDeadQuestions' AS Message
  FROM Posts
  WHERE
    PostTypeId = 1
    AND
    CreationDate < DATEADD(d, -30, @now)
    AND
    AnswerCount = 0
    AND
    Score < 0

  UNION

  SELECT
    Id,
    DATEADD(d, 365, CreationDate) AS DeletionDate,
    'RemoveAbandonedQuestions' AS Message
  FROM Posts
  WHERE
    PostTypeId = 1
    AND
    CreationDate < DATEADD(d, -365, @now)
    AND
    AnswerCount = 0
    AND
    (
      (Score <= 0 AND OwnerUserId IS NOT NULL)
      OR
      (Score <= 1 AND OwnerUserId IS NULL)
    )
    AND
      ViewCount < 1.5 * DATEDIFF(d, CreationDate, @now)
    AND
      Id NOT IN (SELECT Id FROM QMin2C)
)
SELECT
  Raw.Id AS [Post Link],
  Posts.OwnerUserId AS [User Link],
  Posts.Score,
  Raw.Message,
  Posts.CreationDate,
  Raw.DeletionDate
FROM Raw, Posts
WHERE
  Raw.Id = Posts.Id
  AND
  Raw.Id NOT IN (SELECT PostId FROM Locked)
  AND
  Raw.Id NOT IN (SELECT PostId FROM Migrated)
ORDER BY Raw.DeletionDate DESC

fork query

edited jan 4 22

created jan 4 22 peterh (2)