User pages without a user account on Bengali Wikipedia

Fork of Bnwiki User pages without a user account by MdsShakil

This query is marked as a draft This query has been published by Aishik Rehman.

(anonymous users and redirects excluded)

Note that the table `user` doesn't seem to contain entries for users with zero edits.

TODO: could perhaps get better info from table `actor`.

SQL

USE bnwiki_p;
SELECT 
	page_namespace, 
    REPLACE(page_title,'_',' ') AS page_title, 
    GROUP_CONCAT(REPLACE(cl_to,'_',' ') SEPARATOR ' | ') AS cl_to
FROM `page` LEFT JOIN `categorylinks` ON page_id=cl_from
WHERE 
	page_is_redirect=0 AND
	page_namespace IN (2, 3) AND
    page_title NOT REGEXP '([1-9][0-9\.]+[0-9]|[1-9A-F][0-9A-F:]+[0-9A-F])(/[0-9]+)?' AND
    NOT EXISTS (SELECT * FROM `user` WHERE
                user_name=REGEXP_REPLACE(REPLACE(page_title,'_',' '), ' */.*$', '')
               )
GROUP BY page_id
ORDER BY page_id DESC	# new pages on top

SQL

Checking query status...