Untitled query #90473

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')
)
SELECT
    p.page_title,
    fp.fp_stable AS last_reviewed_rev_id,
    fp.fp_pending_since AS pending_since
FROM flaggedpages fp
JOIN page p ON p.page_id = fp.fp_page_id
WHERE p.page_namespace = 0
  AND fp.fp_pending_since IS NOT NULL
  AND 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 privileged_actors)
  )
ORDER BY fp.fp_pending_since DESC;
SQL

Checking query status...
