CSD U2 worthy pages

SQL

select ROW_NUMBER() OVER(order by page_title) num, -- get row number
       case when page_namespace = 2 then CONCAT('[[User:', replace(page_title, '_', ' '), ']]') -- Format as wikilink, check whether it is in User namespace
            else CONCAT('[[User talk:', replace(page_title, '_', ' '), ']]') -- Format as wikilink, check whether it is in User talk namespace
       end as 'U2 worthy pages',
       page_len as 'Page length',
       DATE_FORMAT(rev_timestamp, '%Y-%m-%d %H:%i:%s') as 'Creation date',
       actor_name as 'Page creator'
from revision -- Use revision table
join (select -- join with page table
      page_namespace,
      page_id,
      page_title,
      page_len
      from page
      left join `user` -- join with user table, use ` to escape user keyword
      on user_name = replace(SUBSTRING_INDEX(page_title, '/', 1), '_', ' ') -- replace underscore with space
      where page_namespace in (2, 3) -- filter User and User talk namespaces
      and page_is_redirect = 0 -- exclude redirects
      -- AND page_title NOT RLIKE '(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)'
      and page_title not regexp '([0-9A-E][0-9A-E][0-9A-E]:[0-9A-E].|[1-9].|[1-9]{2}.|[1-9]{3}.)' -- exclude titles matching IP addresses
      and page_title not like '0.0.0.0' -- 3 lines excluding valid pages from this query
      and page_title not like 'Unknown_user'
      and page_title not like 'Global_rename_script'
      and ISNULL(user_name)) as pgtmp -- Base pagename is a non existent username
 on pgtmp.page_id = rev_page -- join on page_id
 and rev_timestamp = (select -- filter the earliest timestamp from page history
                      MIN(rev_timestamp)
                      from revision
                      where rev_page = pgtmp.page_id)
join actor on rev_actor = actor_id -- join with actor table to get page creator's name
order by page_title asc; -- arrange in ascending order
SQL

Checking query status...
