Untitled query #64749

SQL

WITH privileged_actors AS (
    SELECT actor_id
    FROM user_groups
    JOIN actor ON actor_user = ug_user
    WHERE ug_group IN ('autoreview', 'editor', 'bot')
),
user_pending_pages AS (
    SELECT DISTINCT p.page_id, p.page_title, a.actor_id
    FROM flaggedpages fp
    JOIN page p ON fp.fp_page_id = p.page_id
    JOIN revision r ON r.rev_page = p.page_id
    JOIN actor a ON r.rev_actor = a.actor_id
    WHERE p.page_namespace = 0
      AND fp.fp_pending_since IS NOT NULL
),
all_allowed_actors AS (
    SELECT actor_id FROM privileged_actors
    UNION
    SELECT actor_id FROM user_pending_pages
)
SELECT
    p.page_title,
    fp.fp_stable AS last_reviewed_rev_id,
    fp.fp_pending_since AS pending_since
FROM user_pending_pages upp
JOIN page p ON p.page_id = upp.page_id
JOIN flaggedpages fp ON fp.fp_page_id = p.page_id
WHERE NOT EXISTS (
    SELECT 1
    FROM revision r
    WHERE r.rev_page = p.page_id
      AND r.rev_id > fp.fp_stable
      AND r.rev_actor NOT IN (SELECT actor_id FROM all_allowed_actors)
)
ORDER BY fp.fp_pending_since DESC;
SQL

Checking query status...
