-- Query to find the longest pages across ALL Wikipedia namespaces

-- This query sorts pages by length across all namespaces and shows the full URL

-- Run this on the 'enwiki_p' database in Quarry

SELECT

  CONCAT('https://en.wikipedia.org/wiki/', 

    IF(page_namespace = 0, 

      '', 

      CASE

        WHEN page_namespace = 1 THEN 'Talk:'

        WHEN page_namespace = 2 THEN 'User:'

        WHEN page_namespace = 3 THEN 'User_talk:'

        WHEN page_namespace = 4 THEN 'Wikipedia:'

        WHEN page_namespace = 5 THEN 'Wikipedia_talk:'

        WHEN page_namespace = 6 THEN 'File:'

        WHEN page_namespace = 7 THEN 'File_talk:'

        WHEN page_namespace = 8 THEN 'MediaWiki:'

        WHEN page_namespace = 9 THEN 'MediaWiki_talk:'

        WHEN page_namespace = 10 THEN 'Template:'

        WHEN page_namespace = 11 THEN 'Template_talk:'

        WHEN page_namespace = 12 THEN 'Help:'

        WHEN page_namespace = 13 THEN 'Help_talk:'

        WHEN page_namespace = 14 THEN 'Category:'

        WHEN page_namespace = 15 THEN 'Category_talk:'

        WHEN page_namespace = 100 THEN 'Portal:'

        WHEN page_namespace = 101 THEN 'Portal_talk:'

        WHEN page_namespace = 118 THEN 'Draft:'

        WHEN page_namespace = 119 THEN 'Draft_talk:'

        WHEN page_namespace = 828 THEN 'Module:'

        WHEN page_namespace = 829 THEN 'Module_talk:'

        ELSE CONCAT('Special:MyLanguage/NS', page_namespace, ':')

      END

    ),

    REPLACE(page_title, ' ', '_')

  ) AS Full_URL,

  page_len AS Length_in_Bytes

FROM page

WHERE page_is_redirect = 0    -- Exclude redirects

  AND page_len > 0            -- Only pages with content

ORDER BY page_len DESC        -- Sort by descending length

LIMIT 100;                    -- Show top 100 results