Untitled query #87204

SQL

SELECT 
    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 you can choose to 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
-- JOIN to include only pages in the Featured_articles category
JOIN 
    categorylinks ON page.page_id = categorylinks.cl_from
WHERE 
    page.page_namespace = 0
    AND page_is_redirect = 0
    AND page_props.pp_page IS NULL  -- Exclude disambiguation pages
    AND page.page_len > 0  -- Avoid division by zero
    AND categorylinks.cl_to = 'Use_dmy_dates_from_July_2019' 
GROUP BY 
    page.page_id
ORDER BY 
    ratio DESC;
SQL

Checking query status...
