Untitled query #84716

SQL

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

    UNION ALL

    SELECT cl_to, root_category
    FROM categorylinks
    JOIN subcategories ON categorylinks.cl_from = (SELECT page_id FROM page WHERE page_title = subcategories.subcategory AND page_namespace = 14)
),
category_articles AS (
    SELECT cl_to AS category, page_id, page_title
    FROM page
    JOIN categorylinks ON page.page_id = categorylinks.cl_from
    WHERE page.page_namespace = 0
        AND page.page_is_redirect = 0
        AND categorylinks.cl_to IN (SELECT subcategory FROM subcategories)
),
article_counts AS (
    SELECT 
        ca.category,
        COUNT(ca.page_id) AS total_articles,
        COUNT(CASE WHEN NOT EXISTS (
            SELECT 1
            FROM langlinks ll
            WHERE ll.ll_from = ca.page_id
                AND ll.ll_lang = 'ar'
        ) THEN 1 END) AS articles_not_in_arabic
    FROM category_articles ca
    GROUP BY ca.category
)

SELECT 
    c.category,
    c.total_articles,
    c.articles_not_in_arabic
FROM article_counts c
ORDER BY c.articles_not_in_arabic DESC;
SQL

Checking query status...
