Fork of
Untitled query #87534
by Liz
This query is marked as a draft
This query has been published
by Liz.
SQL
x
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 184 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)
)
)
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 184 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.
All SQL code is licensed under CC0 License.