Untitled query #84715

SQL

WITH RECURSIVE subcategories AS (
    SELECT cl_to AS subcategory
    FROM categorylinks
    WHERE cl_from = (SELECT page_id FROM page WHERE page_title = 'Iraq' AND page_namespace = 14)

    UNION ALL

    SELECT cl_to
    FROM categorylinks
    JOIN subcategories ON categorylinks.cl_from = (SELECT page_id FROM page WHERE page_title = subcategories.subcategory AND page_namespace = 14)
)

SELECT CONCAT (
        "[[:en:",
        page.page_title,
        "]]||",
        COUNT(*) AS link_count,
        "||",
        page.page_len
        )
FROM page
JOIN categorylinks ON page.page_id = categorylinks.cl_from
JOIN langlinks ON page.page_id = langlinks.ll_from
WHERE page.page_namespace = 0
    AND page.page_is_redirect = 0
    AND categorylinks.cl_to IN (SELECT subcategory FROM subcategories)
    AND NOT EXISTS (
        SELECT *
        FROM langlinks AS t
        WHERE t.ll_lang = 'ar'
            AND t.ll_from = langlinks.ll_from
        )
GROUP BY page.page_id
ORDER BY page.page_len DESC, link_count DESC
LIMIT 2000;
SQL

Checking query status...
