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 William Avery.

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, 'User:', 'Usertalk:'), p.page_title) AS page
FROM   actor a,
       page p,
       user u
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 EXISTS (SELECT COUNT(*)
                   FROM   revision_userindex rp
                   WHERE  p.page_id = rp.rev_page
                          AND a.actor_id = rp.rev_actor
                  HAVING COUNT(*) = 1)
       AND NOT EXISTS (SELECT 1
                       FROM   revision_userindex rp
                       WHERE  p.page_id = rp.rev_page
                              AND a.actor_id != rp.rev_actor)
       AND p.page_namespace IN ( 2, 3 )
       AND u.user_id = a.actor_user
LIMIT  100

SQL

Checking query status...