- Quarry

This query is marked as a draft This query has been published by Explicit.
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
),
LastHumanEdit AS (
  SELECT
    r.rev_page,
    MAX(r.rev_timestamp) AS last_human_edit
  FROM
    revision r
  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 r.rev_page
)

SELECT
  CONCAT('Draft:', p.page_title) AS page_title,
  r.rev_timestamp AS timestamp,
  OldestRevision.actor_name AS actor_name_
FROM
  page p
  JOIN templatelinks tl ON tl.tl_from = p.page_id
  JOIN linktarget lt ON lt.lt_id = tl.tl_target_id AND lt.lt_namespace = 10 AND lt.lt_title IN ('AFC_submission', 'AfC_submission')
  LEFT JOIN revision r ON p.page_latest = r.rev_id
  LEFT JOIN OldestRevision ON p.page_id = OldestRevision.page_id
  LEFT JOIN LastHumanEdit ON p.page_id = LastHumanEdit.rev_page
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 (
    (LastHumanEdit.last_human_edit IS NOT NULL AND LastHumanEdit.last_human_edit < DATE_SUB(NOW(), INTERVAL 180 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 180 DAY)
    )
  )

UNION

SELECT
  CONCAT('User:', p.page_title) AS page_title,
  r.rev_timestamp AS timestamp,
  OldestRevision.actor_name AS actor_name_
FROM
  page p
  JOIN templatelinks tl ON tl.tl_from = p.page_id
  JOIN linktarget lt ON lt.lt_id = tl.tl_target_id AND lt.lt_namespace = 10 AND lt.lt_title IN ('AFC_submission', 'AfC_submission')
  LEFT JOIN revision r ON p.page_latest = r.rev_id
  LEFT JOIN OldestRevision ON p.page_id = OldestRevision.page_id
  LEFT JOIN LastHumanEdit ON p.page_id = LastHumanEdit.rev_page
WHERE
  tl.tl_from_namespace = 2
  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 (
    (LastHumanEdit.last_human_edit IS NOT NULL AND LastHumanEdit.last_human_edit < DATE_SUB(NOW(), INTERVAL 180 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 180 DAY)
    )
  )
ORDER BY
  timestamp;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
