USE enwiki_p;

​

SELECT 

    @rank := @rank + 1 AS rank,

    CONCAT("https://en.wikipedia.org/wiki/User_talk:", REPLACE(p.page_title, " ", "_")) AS page_url,

    FORMAT(p.page_len, 0) AS page_length, -- Formats page length with commas

    FORMAT(u.user_editcount, 0) AS user_edit_count -- Formats edit count with commas

FROM 

    page p

LEFT JOIN 

    user u ON u.user_name = REPLACE(p.page_title, "_", " ") -- Match user_name to page_title

CROSS JOIN 

    (SELECT @rank := 0) AS r -- Initialize rank variable

WHERE 

    p.page_namespace = 3 -- User Talk pages

    AND INSTR(p.page_title, '/') = 0 -- Exclude subpages

ORDER BY 

    p.page_len DESC

LIMIT 500;