List of short articles a user created with edits from one other user

Fork of List of articles a user created without edits from other users by MJL

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

SQL

USE scowiki_p;
SELECT DISTINCT
    p.page_title AS "page_title",
    u.rev_timestamp as "rev_timestamp"
FROM
    revision_userindex u
JOIN page p ON p.page_id = u.rev_page
WHERE u.rev_parent_id IN (
    select distinct
        r.rev_id
    from 
        revision_userindex r
    where 
        r.rev_actor = 40
    and
        r.rev_parent_id = 0
)
AND p.page_latest in (
    select distinct
        r.rev_id
    from 
        revision_userindex r
    join page e ON e.page_id = r.rev_page
    where 
        e.page_namespace = 0
    and
        e.page_is_redirect = 0
    and
        r.rev_parent_id = u.rev_parent_id
)
AND p.page_is_new = 0
AND p.page_len < 1000
ORDER BY
    rev_timestamp desc;

SQL

Checking query status...