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.

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(rp.rev_actor)
                   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...