Untitled query #82899

SQL

-- Step 1: Filter and join categorylinks and templatelinks tables early
WITH filtered_pages AS (
    SELECT cl_from AS page_id
    FROM categorylinks
    WHERE cl_to = 'All_articles_with_unsourced_statements'
),
citation_needed_links AS (
    SELECT tl_from AS page_id
    FROM templatelinks
    JOIN linktarget ON templatelinks.tl_target_id = linktarget.lt_id
    WHERE lt_title = 'Citation_needed'
      AND templatelinks.tl_from_namespace = 0
)

-- Step 2: Join the filtered tables with the page table
SELECT page.page_title, COUNT(*) AS citation_needed_count
FROM page
JOIN filtered_pages ON page.page_id = filtered_pages.page_id
JOIN citation_needed_links ON page.page_id = citation_needed_links.page_id
WHERE page.page_namespace = 0
GROUP BY page.page_title
ORDER BY citation_needed_count DESC
LIMIT 100;
SQL

Checking query status...
