Patrolled rcs - Quarry

SQL

-- Parameters:
--     lp_num: log params key-value pair (differs from wiki to wiki)

WITH patrol_logs AS (
    SELECT
        CAST(log_id AS INT) AS log_id,
        CAST(log_timestamp AS DATETIME) AS log_ts,
        DATE(log_timestamp) AS log_dt,
        CASE
            WHEN log_namespace = 0 THEN TRUE
            ELSE FALSE
        END AS is_ns0,
        log_title,
        CAST(log_actor AS INT) AS log_actor,
        SUBSTRING_INDEX(
            -- log_params array structure is varied across various wikis
            -- we will pass the required parameter dynamically
            SUBSTRING_INDEX(log_params, '"4::curid";s:9:"', -1),
            '"', 
            1
        ) AS curr_rev_id
    FROM
        logging logs
    WHERE
        log_type = 'patrol'
        AND log_page IS NOT NULL
        AND DATE(log_timestamp) = CURDATE() - INTERVAL 1 DAY
)

SELECT
    pls.*,
    rc_id,
    CAST(rc_timestamp AS DATETIME) AS rc_ts,
    DATE(rc_timestamp) AS rc_dt,
    rc_new,
    (actor_user IS NULL) AS is_anon
FROM
    patrol_logs pls
JOIN
    recentchanges rc ON pls.curr_rev_id = rc.rc_this_oldid
        AND pls.log_title = rc.rc_title
JOIN
    actor ra ON rc.rc_actor = ra.actor_id
WHERE
    rc_patrolled = 1
    AND rc_bot = 0
    AND curr_rev_id REGEXP '^[0-9]+$'
SQL

Checking query status...
