enwiki pages by link density

SQL

SELECT 
    page.page_id,
    page.page_title,
    page.page_len,
    link_data.internal_link_count,
    CASE 
        WHEN IFNULL(link_data.internal_link_count, 0) > 0 
        THEN page.page_len / link_data.internal_link_count 
        ELSE NULL  -- or return 0 instead of NULL if preferred
    END AS ratio
FROM 
    page
-- LEFT JOIN to exclude disambiguation pages
LEFT JOIN 
    page_props ON page.page_id = page_props.pp_page 
    AND page_props.pp_propname = 'disambiguation'
-- LEFT JOIN to get internal link count data
LEFT JOIN (
    SELECT 
        pl_from AS page_id,
        COUNT(*) AS internal_link_count
    FROM 
        pagelinks
    GROUP BY 
        pl_from
) AS link_data ON link_data.page_id = page.page_id
-- LEFT JOIN to exclude list-class list articles using talk pages
LEFT JOIN (
    SELECT 
        page_id AS talk_page_id
    FROM 
        categorylinks
    WHERE 
        cl_to = 'List-Class_List_articles'
) AS talk_page_exclusion ON talk_page_exclusion.talk_page_id = page.page_id - 1
WHERE 
    page.page_namespace = 0
    AND page_is_redirect = 0
    AND page.page_id < 5000000
    AND page_props.pp_page IS NULL  -- Exclude disambiguation pages
    AND talk_page_exclusion.talk_page_id IS NULL  -- Exclude list-class list articles
    AND page.page_len > 0  -- Avoid division by zero
GROUP BY 
    page.page_id
ORDER BY 
    ratio DESC;
SQL

Checking query status...
