Untitled query #65083

SQL

        WITH bots AS (
            SELECT user_id
            FROM user
            JOIN user_groups ON user_id = ug_user
            WHERE ug_group = 'bot'
        )

        SELECT page_namespace, page_title
        FROM page
                 LEFT JOIN revision_userindex ON rev_page = page_id
                 LEFT JOIN actor_revision ON actor_id = rev_actor
        WHERE page_namespace = 118
          AND page_is_redirect = 0
          AND (actor_user IS NULL OR actor_user NOT IN (SELECT * FROM bots))
        GROUP BY page_id
        HAVING MAX(rev_timestamp) < DATE_FORMAT(UTC_DATE() - INTERVAL 6 MONTH + INTERVAL 1 DAY, '%Y%m%d%H%i%S')

        UNION

        SELECT page_namespace, page_title
        FROM page
                 LEFT JOIN revision_userindex ON rev_page = page_id
                 LEFT JOIN actor_revision ON actor_id = rev_actor
                 JOIN templatelinks ON tl_from = page_id AND tl_namespace = 10 AND tl_title = 'AfC_submission'
        WHERE page_namespace = 2
          AND page_is_redirect = 0
          AND (actor_user IS NULL OR actor_user NOT IN (SELECT * FROM bots))
        GROUP BY page_id
        HAVING MAX(rev_timestamp) < DATE_FORMAT(UTC_DATE() - INTERVAL 6 MONTH + INTERVAL 1 DAY, '%Y%m%d%H%i%S')

        UNION 
        
        -- currently eligible pages that have already been G13-tagged won't show up in the above 
        -- timestamp based lookup
        SELECT page_namespace, page_title
        FROM page
        JOIN categorylinks ON cl_from = page_id
        WHERE cl_to = 'Candidates_for_speedy_deletion_as_abandoned_drafts_or_AfC_submissions'
SQL

Checking query status...
