Untitled query #64748

SQL

WITH user_actor AS (
    SELECT actor_id
    FROM actor
    WHERE actor_name = 'Duppertip'
),
allowed_actors AS (
    SELECT actor_id
    FROM user_actor
    UNION
    SELECT actor_id
    FROM user_groups
    JOIN actor ON actor_user = ug_user
    WHERE ug_group IN ('autoreview', 'editor', 'bot')
)
SELECT
    DISTINCT 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
JOIN revision r ON r.rev_page = p.page_id
JOIN user_actor ua ON r.rev_actor = ua.actor_id
WHERE p.page_namespace = 0
  AND fp.fp_pending_since IS NOT NULL
  AND r.rev_timestamp >= fp.fp_pending_since
  AND r.rev_id > fp.fp_stable
  AND NOT EXISTS (
      SELECT 1
      FROM revision r2
      WHERE r2.rev_page = p.page_id
        AND r2.rev_id > fp.fp_stable
        AND r2.rev_actor NOT IN (SELECT actor_id FROM allowed_actors)
  )
ORDER BY fp.fp_pending_since DESC;
SQL

Checking query status...
