All deleted drafts starting "Outline "

All deleted pages in the draft namespace whose name starts with "Outline ", the number of deleted revisions, timestamps of the first and last deleted revision, the editors of the deleted revisions, whether pages with the same title currently exist in the draft and article namespaces, and whether those are redirects.

For [[WP:Deletion review/Log/2023 October 30#Request for help undeleting all the unknown outlines in draft space]]

SQL

SELECT CONCAT('[[Draft:', REPLACE(ar_title, '_', ' '), ']]') AS 'deleted draft',
       COUNT(*) AS '#/revs',
       DATE_FORMAT(MIN(ar_timestamp), '%Y/%m/%d %H:%i:%s') AS 'first',
       DATE_FORMAT(MAX(ar_timestamp), '%Y/%m/%d %H:%i:%s') AS 'last',
       COUNT(DISTINCT ar_actor) AS '#/authors',
       CONCAT(IF(draft.page_id IS NOT NULL,
                 CONCAT('[[Draft:',
                        REPLACE(ar_title, '_', ' '),
                        ']]',
                        IF(draft.page_is_redirect, ' (redirect)', ''),
                        IF(article.page_id IS NOT NULL, ', ', '')),
                 ''),
              IF(article.page_id IS NOT NULL,
                 CONCAT('[[',
                        REPLACE(ar_title, '_', ' '),
                        ']]',
                        IF(article.page_is_redirect, ' (redirect)', '')),
                 '')) AS 'exists at'
FROM archive
LEFT JOIN page AS draft ON draft.page_namespace = 118 AND draft.page_title = ar_title
LEFT JOIN page AS article ON article.page_namespace = 0 AND article.page_title = ar_title
WHERE ar_namespace = 118
  AND ar_title LIKE 'Outline\_%'
GROUP BY ar_title;

SQL

Checking query status...