- Quarry

This query is marked as a draft This query has been published by DreamRimmer.
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
  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 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
  LEFT JOIN LastHumanEdit ON p.page_id = LastHumanEdit.rev_page
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 (
    (LastHumanEdit.last_human_edit IS NOT NULL AND LastHumanEdit.last_human_edit < 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 184 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...
