Redirects to deleted pages with information on last deletion in English Wiktionary

SQL

-- Generated from siteinfo-namespaces in the dump;
-- namespaces.name is the local name, not the canonical name.
with namespaces(id, name) as (
values
    (-2, 'Media'),
    (-1, 'Special'),
    (0, ''),
    (1, 'Talk'),
    (2, 'User'),
    (3, 'User talk'),
    (4, 'Wiktionary'),
    (5, 'Wiktionary talk'),
    (6, 'File'),
    (7, 'File talk'),
    (8, 'MediaWiki'),
    (9, 'MediaWiki talk'),
    (10, 'Template'),
    (11, 'Template talk'),
    (12, 'Help'),
    (13, 'Help talk'),
    (14, 'Category'),
    (15, 'Category talk'),
    (90, 'Thread'),
    (91, 'Thread talk'),
    (92, 'Summary'),
    (93, 'Summary talk'),
    (100, 'Appendix'),
    (101, 'Appendix talk'),
    (102, 'Concordance'),
    (103, 'Concordance talk'),
    (104, 'Index'),
    (105, 'Index talk'),
    (106, 'Rhymes'),
    (107, 'Rhymes talk'),
    (108, 'Transwiki'),
    (109, 'Transwiki talk'),
    (110, 'Thesaurus'),
    (111, 'Thesaurus talk'),
    (114, 'Citations'),
    (115, 'Citations talk'),
    (116, 'Sign gloss'),
    (117, 'Sign gloss talk'),
    (118, 'Reconstruction'),
    (119, 'Reconstruction talk'),
    (828, 'Module'),
    (829, 'Module talk'),
    (2300, 'Gadget'),
    (2301, 'Gadget talk'),
    (2302, 'Gadget definition'),
    (2303, 'Gadget definition talk')
)
select
    source_page.page_id,
    source_page.page_namespace,
    source_page.page_title,
    replace(concat(
        if(source_namespace.name = '', '', concat(source_namespace.name, ':')),
        source_page.page_title
    ), '_', ' ') as full_title,
    rd_namespace,
    rd_title,
    replace(concat(
        if(target_namespace.name = '', '', concat(target_namespace.name, ':')),
        target_page.page_title
    ), '_', ' ') as target_full_title,
    max(log_timestamp)
from logging
left join redirect on rd_namespace = log_namespace and rd_title = log_title
join page as source_page on source_page.page_id = rd_from
left join page as target_page on target_page.page_namespace = rd_namespace and target_page.page_title = rd_title
join namespaces as source_namespace on source_namespace.id = source_page.page_namespace
join namespaces as target_namespace on target_namespace.id = rd_namespace
where log_type = 'delete' and log_action = 'delete' and target_page.page_id is null
group by source_page.page_id;
SQL

Checking query status...
