Drafts with the same title as deleted articles in mainspace

SQL

WITH SearchResults AS (
    SELECT 
        p.page_title,
        p.page_len,
        r.old_text AS content,
        -- Calculate the Levenshtein distance for each instance
        LEVENSHTEIN('pronunciation', SUBSTRING_INDEX(
            SUBSTRING_INDEX(
                LOWER(r.old_text),
                'pronunciation',
                1
            ),
            ' ',
            -1
        )) as distance
    FROM page p
    JOIN revision r ON p.page_latest = r.rev_id
    JOIN text t ON r.rev_text_id = t.old_id
    WHERE p.page_namespace = 0  -- Main namespace only
    AND p.page_is_redirect = 0  -- Exclude redirects
    AND r.old_text REGEXP '\\b[[:alpha:]]*pro[[:alpha:]]*n[[:alpha:]]*n[[:alpha:]]*c[[:alpha:]]*t[[:alpha:]]*n[[:alpha:]]*\\b'  -- Rough pattern match
    AND r.old_text NOT REGEXP 'pronunciation'  -- Exclude correct spellings
)
SELECT 
    page_title AS article_name,
    page_len AS article_length,
    SUBSTRING(content, GREATEST(1, LOCATE(REGEXP '\\b[[:alpha:]]*pro[[:alpha:]]*n[[:alpha:]]*n[[:alpha:]]*c[[:alpha:]]*t[[:alpha:]]*n[[:alpha:]]*\\b', content) - 50), 100) AS context,
    distance AS levenshtein_distance,
    COUNT(*) OVER () as total_instances
FROM SearchResults
WHERE distance BETWEEN 1 AND 3  -- Adjust threshold as needed
ORDER BY distance ASC, page_len DESC
LIMIT 10;
SQL

Checking query status...
