Deleted portal subpages where the base page exists (more info)

All portal subpages that have at least one deleted revision, where the base portal page exists.

Compared to the previous query, this contains the redirect target and deletion log entries.

For [[Wikipedia talk:WikiProject Portals#Identifying old-style portals]] circa 11 April 2019.

SQL

SELECT DISTINCT
    CONCAT('[[Portal:', p.page_title, ']]') AS 'Existing base page',
    CASE WHEN rd_namespace IS NULL THEN NULL
         WHEN rd_namespace = 100 THEN CONCAT('[[Portal:', rd_title, ']]')
         ELSE CONCAT('[[{{ns:', rd_namespace, '}}:', rd_title, ']]')
    END AS 'Base page redirects to',
    CONCAT('[[Portal:', ar_title, ']]') AS 'Deleted subpage',
    CASE WHEN EXISTS (SELECT 1 FROM page p2
                      WHERE p2.page_namespace = 100
                          AND p2.page_title = ar_title)
         THEN 'Y'
         ELSE 'N'
    END AS 'Deleted subpage exists',
    user_name,
    DATE_FORMAT(log_timestamp, '%Y-%m-%d %H:%i:%s') AS timestamp,
    log_action,
    comment_text
FROM archive
JOIN page p
    ON p.page_namespace = 100 -- Portal:
    AND p.page_title = REGEXP_REPLACE(ar_title, '/.*', '')
LEFT JOIN logging_logindex
    ON log_namespace = 100
    AND log_title = ar_title
    AND log_type = 'delete'
LEFT JOIN user
    ON user_id = log_user
LEFT JOIN comment
    ON comment_id = log_comment_id
LEFT JOIN redirect
    ON rd_from = p.page_id
WHERE ar_namespace = 100
    AND ar_title LIKE '%/%';

SQL

Checking query status...