Existing pages with deleted revisions

SQL

WITH CommonPages AS (
    -- Your existing query to find common pages
    SELECT
        common_page_title,
        common_page_namespace
    FROM (
        SELECT
            page_title AS common_page_title,
            page_namespace AS common_page_namespace
        FROM
            page
        WHERE
            CONCAT(page_namespace, ':', page_title) IN (
                SELECT
                    CONCAT(ar_namespace, ':', ar_title)
                FROM
                    archive
                WHERE
                    ar_title IS NOT NULL AND ar_namespace IS NOT NULL
            )
        UNION
        SELECT
            ar_title AS common_page_title,
            ar_namespace AS common_page_namespace
        FROM
            archive
        WHERE
            CONCAT(ar_namespace, ':', ar_title) IN (
                SELECT
                    CONCAT(page_namespace, ':', page_title)
                FROM
                    page
                WHERE
                    page_title IS NOT NULL AND page_namespace IS NOT NULL
            )
    ) AS common_pages
)

-- Use the CommonPages CTE to append namespace names
SELECT
    CONCAT(
        CASE
            WHEN common_page_namespace = 0 THEN ''
            WHEN common_page_namespace = 1 THEN 'Разговор:'
            WHEN common_page_namespace = 2 THEN 'Корисник:'
            WHEN common_page_namespace = 3 THEN 'Разговор с корисником:'
            WHEN common_page_namespace = 4 THEN 'Википедија:'
            WHEN common_page_namespace = 5 THEN 'Разговор о Википедији:'
            WHEN common_page_namespace = 6 THEN 'Датотека:'
            WHEN common_page_namespace = 7 THEN 'Разговор о датотеци:'
            WHEN common_page_namespace = 8 THEN 'Медијавики:'
            WHEN common_page_namespace = 9 THEN 'Разговор о Медијавикију:'
            WHEN common_page_namespace = 10 THEN 'Шаблон:'
            WHEN common_page_namespace = 11 THEN 'Разговор о шаблону:'
            WHEN common_page_namespace = 12 THEN 'Помоћ:'
            WHEN common_page_namespace = 13 THEN 'Разговор о помоћи:'
            WHEN common_page_namespace = 14 THEN 'Категорија:'
            WHEN common_page_namespace = 15 THEN 'Разговор о категорији:'
            WHEN common_page_namespace = 100 THEN 'Портал:'
            WHEN common_page_namespace = 101 THEN 'Разговор о порталу:'
            WHEN common_page_namespace = 118 THEN 'Нацирт:'
            WHEN common_page_namespace = 119 THEN 'Разговор о нацирту:'
            WHEN common_page_namespace = 710 THEN 'TimedText:'
            WHEN common_page_namespace = 711 THEN 'Разговор о TimedText:'
            WHEN common_page_namespace = 828 THEN 'Модул:'
            WHEN common_page_namespace = 829 THEN 'Разговор о модулу:'
            ELSE ''
        END,
        common_page_title
    ) AS modified_page_title
FROM
    CommonPages;
SQL

Checking query status...
