Fork of Unreviewed changes by trusted users by FoBe
This query is marked as a draft This query has been published by FoBe.

SQL

AخA
 
SELECT
    page1.page_title,
    page1.page_latest
FROM page as page1
INNER JOIN revision AS lastrevision ON page1.page_latest = lastrevision.rev_id -- used for outputting timestamp of last edit, not used in rest of query
INNER JOIN revision AS revision1 ON page1.page_id = revision1.rev_page
INNER JOIN flaggedpage_pending AS flaggedpage_pending1 ON page1.page_id = flaggedpage_pending1.fpp_page_id
INNER JOIN actor AS actor1 ON revision1.rev_actor = actor1.actor_id
INNER JOIN user AS user1 ON actor1.actor_user = user1.user_id
LEFT OUTER JOIN user_groups AS user_groups1 ON ug_user = actor_user
WHERE page1.page_namespace = 0
    AND revision1.rev_timestamp >= flaggedpage_pending1.fpp_pending_since
    AND user_groups1.ug_group IN ('editor','trusted','sysop','bot')
    AND page1.page_id NOT IN ( # exclude edits by anons
        SELECT page2.page_id
        FROM page as page2
        INNER JOIN revision AS revision2 ON page2.page_id = revision2.rev_page
        INNER JOIN actor AS actor2 ON revision2.rev_actor = actor2.actor_id
        WHERE page1.page_id = page2.page_id
            AND revision2.rev_timestamp >= flaggedpage_pending1.fpp_pending_since
            AND actor2.actor_user IS NULL
        )
     AND page1.page_id NOT IN ( # exclude edits by non-trusted users
        SELECT page2.page_id
        FROM page as page2
        INNER JOIN revision AS revision2 ON page2.page_id = revision2.rev_page
        INNER JOIN actor AS actor2 ON revision2.rev_actor = actor2.actor_id
        INNER JOIN user AS user2 ON actor2.actor_user = user2.user_id
        WHERE page1.page_id = page2.page_id
            AND revision2.rev_timestamp >= flaggedpage_pending1.fpp_pending_since
            AND NOT EXISTS (SELECT user_groups2.ug_user FROM user_groups AS user_groups2 WHERE user_groups2.ug_user = user2.user_id AND user_groups2.ug_group IN ('editor','trusted','sysop','bot'))
        )
GROUP BY page1.page_title
UNION
SELECT 
    page1.page_title,
    page1.page_latest
FROM page as page1 #, user_groups
INNER JOIN revision AS revision1 ON page1.page_id = revision1.rev_page
INNER JOIN actor AS actor1 ON revision1.rev_actor = actor1.actor_id
INNER JOIN logging ON (actor1.actor_id=logging.log_actor) 
INNER JOIN user AS user1 ON actor1.actor_user = user1.user_id
LEFT OUTER JOIN user_groups AS user_groups1 ON ug_user = actor_user
WHERE page1.page_namespace = 0
    AND log_action = "create" -- only list new pages
    AND user_groups1.ug_group IN ('editor','trusted','sysop','bot')
    AND page1.page_id NOT IN ( # exclude edits by anons
        SELECT page2.page_id
        FROM page as page2
        INNER JOIN revision AS revision2 ON page2.page_id = revision2.rev_page
        INNER JOIN actor AS actor2 ON revision2.rev_actor = actor2.actor_id
        WHERE page1.page_id = page2.page_id
            AND actor2.actor_user IS NULL
        )
     AND page1.page_id NOT IN ( # exclude edits by non-trusted users
        SELECT page2.page_id
        FROM page as page2
        INNER JOIN revision AS revision2 ON page2.page_id = revision2.rev_page
        INNER JOIN actor AS actor2 ON revision2.rev_actor = actor2.actor_id
        INNER JOIN user AS user2 ON actor2.actor_user = user2.user_id
        WHERE page1.page_id = page2.page_id
            AND NOT EXISTS (SELECT user_groups2.ug_user FROM user_groups AS user_groups2 WHERE user_groups2.ug_user = user2.user_id AND user_groups2.ug_group IN ('editor','trusted','sysop','bot'))
        )
       AND page1.page_id NOT IN ( -- do not list pages which have at least one reviewed revision
         SELECT flaggedpages.fp_page_id
         FROM flaggedpages
         WHERE flaggedpages.fp_page_id = page1.page_id
       )
GROUP BY page1.page_title;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...