rc unpatrolled

SQL

-- Calculates unpatrolled recent changes on a daily basis
-- 15 days is considered as cut-off to fetch the status

WITH base AS (
    SELECT
        CURDATE() AS partition_date,
        CURDATE() - INTERVAL 15 DAY AS log_date,
        rc_id,
        CASE 
            WHEN rc_namespace = 0 THEN TRUE
            ELSE FALSE
        END AS is_ns0,
        rc_new,
        rc_this_oldid,
        CASE
            WHEN rc_patrolled = 0 THEN 'unpatrolled'
            WHEN rc_patrolled = 1 THEN 'patrolled'
            WHEN rc_patrolled = 2 THEN 'autopatrolled'
        END AS patrol_status
    FROM
        recentchanges rc
    WHERE
        rc_type IN (0, 1, 6)
        AND NOT rc_bot
        AND DATE(rc_timestamp) = CURDATE() - INTERVAL 15 DAY
)

SELECT
    partition_date, 
    log_date,
    is_ns0,
    rc_new AS is_page_creation,
    patrol_status,
    COUNT(DISTINCT rc_id) AS revision_count
FROM
    base
GROUP BY
    is_ns0,
    rc_new,
    patrol_status
SQL

Checking query status...
