Untitled query #87484

SQL

WITH OldestRevision AS (
  SELECT
    p.page_id,
    p.page_namespace,
    MIN(r.rev_timestamp) AS first_revision_time,
    MIN(a.actor_name) AS actor_name
  FROM
    page p
    JOIN revision r ON p.page_id = r.rev_page
    JOIN actor a ON r.rev_actor = a.actor_id
  WHERE
    a.actor_name NOT LIKE '%bot' 
    AND a.actor_name NOT LIKE '%Bot' 
    AND a.actor_name NOT LIKE '%BOT'
  GROUP BY p.page_id, p.page_namespace
)

SELECT
  CONCAT('Draft:', p.page_title) AS page_title,
  r.rev_timestamp AS timestamp,
  OldestRevision.actor_name AS actor_name_
FROM
  page p
  LEFT JOIN revision r ON p.page_latest = r.rev_id
  LEFT JOIN OldestRevision ON p.page_id = OldestRevision.page_id
WHERE
  p.page_namespace = 118
  AND p.page_is_redirect = 0
  AND NOT EXISTS (
    SELECT
      1
    FROM
      page
    WHERE
      page_namespace = 4
      AND page_title = CONCAT('Miscellany_for_deletion/Draft:', p.page_title)
  )
  AND (
    r.rev_timestamp < DATE_SUB(NOW(), INTERVAL 153 DAY)
    OR (
      EXISTS (
        SELECT 1
        FROM revision r2
        JOIN actor a2 ON r2.rev_actor = a2.actor_id
        WHERE r2.rev_page = p.page_id
          AND r2.rev_timestamp < r.rev_timestamp
          AND a2.actor_name NOT LIKE '%bot'
          AND a2.actor_name NOT LIKE '%Bot'
          AND a2.actor_name NOT LIKE '%BOT'
        ORDER BY r2.rev_timestamp DESC
        LIMIT 1
      )
      AND r.rev_timestamp < DATE_SUB(NOW(), INTERVAL 153 DAY)
    )
  )

UNION

SELECT
  CONCAT('User:', p.page_title) AS page_title,
  r.rev_timestamp AS timestamp,
  OldestRevision.actor_name AS actor_name_
FROM
  templatelinks tl
  LEFT JOIN page p ON tl.tl_from = p.page_id
  LEFT JOIN revision r ON p.page_latest = r.rev_id
  LEFT JOIN OldestRevision ON p.page_id = OldestRevision.page_id
WHERE
  tl.tl_from_namespace = 2
  AND tl.tl_target_id IN (
    SELECT
      lt_id
    FROM
      linktarget
    WHERE
      lt_namespace = 10
      AND lt_title IN ('AFC_submission', 'AfC_submission')
  )
  AND p.page_is_redirect = 0
  AND NOT EXISTS (
    SELECT
      1
    FROM
      page
    WHERE
      page_namespace = 4
      AND page_title = CONCAT('Miscellany_for_deletion/User:', p.page_title)
  )
  AND (
    r.rev_timestamp < DATE_SUB(NOW(), INTERVAL 153 DAY)
    OR (
      EXISTS (
        SELECT 1
        FROM revision r2
        JOIN actor a2 ON r2.rev_actor = a2.actor_id
        WHERE r2.rev_page = p.page_id
          AND r2.rev_timestamp < r.rev_timestamp
          AND a2.actor_name NOT LIKE '%Lugnuts'
          AND a2.actor_name NOT LIKE '%Bot'
          AND a2.actor_name NOT LIKE '%BOT'
        ORDER BY r2.rev_timestamp DESC
        LIMIT 1
      )
      AND r.rev_timestamp < DATE_SUB(NOW(), INTERVAL 153 DAY)
    )
  )
ORDER BY
  timestamp;
SQL

Checking query status...
