Possibly using Wikipedia as webhost (sample)

Fork of Possibly using Wikipedia as webhost (sample) by William Avery

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

List of pages in User and Usertalk namespaces that have only been edited by one user, where that editor has not edited mainspace at all

SQL

SELECT DISTINCT u.user_name AS user,
                Concat(IF(p.page_namespace = 2, 'کاربر:', 'بحث کاربر:'), p.page_title) AS page
FROM   actor a,
       page p,
       user u,
       revision_userindex rp
WHERE  actor_user IS NOT NULL
       AND NOT EXISTS (SELECT 1
                       FROM   revision_userindex ru,
                              page p
                       WHERE  actor_user IS NOT NULL
                              AND a.actor_id = ru.rev_actor
                              AND p.page_id = ru.rev_page
                              AND p.page_namespace = 0)
       AND NOT EXISTS (SELECT 1
                       FROM   revision rv
                       WHERE  rv.rev_page = p.page_id
                         AND  rv.rev_id <> rp.rev_id)
       AND p.page_namespace IN ( 2, 3 )
       AND u.user_id = a.actor_user
       AND p.page_id = rp.rev_page
       AND a.actor_id = rp.rev_actor
       AND p.page_latest = rp.rev_id
       AND rp.rev_page = p.page_id
LIMIT  100

SQL

Checking query status...