Untitled query #88013

SQL

WITH pia_titles AS (
    SELECT
        p.page_title
    FROM
        linktarget lt
    JOIN templatelinks tl ON tl.tl_target_id = lt.lt_id
    JOIN page p ON p.page_id = tl.tl_from
    WHERE
        lt.lt_namespace = 10 -- Template
        AND lt.lt_title IN ("ArbCom_Arab-Israeli_enforcement", "Contentious_topics/Arab-Israeli_talk_notice")
        AND page_namespace = 1
        AND page_is_redirect = 0
    UNION
    SELECT
        page_title
    FROM
        page
    JOIN categorylinks israel ON page_id = israel.cl_from AND israel.cl_to = "WikiProject_Israel_articles"
    JOIN categorylinks palestine ON page_id = palestine.cl_from AND palestine.cl_to = "WikiProject_Palestine_articles"
    WHERE
        page_namespace = 1
        AND page_is_redirect = 0
),
pia AS (
    SELECT
        p1.page_id,
        p1.page_title,
        p1.page_namespace
    FROM
        pia_titles pt
    JOIN page p1 ON p1.page_title = pt.page_title
        AND p1.page_namespace IN (0, 1)
        AND p1.page_is_redirect = 0
),
pia_final AS (
    SELECT
        'PIA' AS area,
        ar.actor_id,
        -- convert(ar.actor_name using utf8mb4) actor_name,
        pia.page_namespace,
        COUNT(r.rev_id) AS rev_count,
        SUM(CASE WHEN ctd.ctd_name = 'mw-reverted' THEN 1 ELSE 0 END) AS reverted,
        SUM(CASE WHEN ctd.ctd_name IN ('mw-undo', 'mw-rollback') THEN 1 ELSE 0 END) AS reverts,
        SUM(CASE WHEN ctd.ctd_name IN ('mw-undo', 'mw-rollback') 
                 AND EXISTS (
                     SELECT 1 
                     FROM change_tag_def ctd_inner 
                     WHERE ctd_inner.ctd_name = 'mw-reverted' 
                     AND ctd_inner.ctd_id = ctd.ctd_id
                 ) THEN 1 ELSE 0 END) AS reverts_and_reverted
    FROM
        actor_revision AS ar
    JOIN revision_userindex AS r ON r.rev_actor = ar.actor_id
    JOIN pia ON pia.page_id = r.rev_page
    LEFT JOIN change_tag ON r.rev_id = change_tag.ct_rev_id 
    LEFT JOIN change_tag_def AS ctd ON change_tag.ct_tag_id = ctd.ctd_id
    WHERE
        DATE(r.rev_timestamp) BETWEEN '2023-10-07' AND '2024-10-06'
    GROUP BY
        page_namespace
)
SELECT
    -- actor_name,
    page_namespace,
    rev_count,
    reverts,
    ROUND(100.0 * reverts / rev_count, 2) AS percent_reverts,
    reverted,
    ROUND(100.0 * reverted / rev_count, 2) AS percent_reverted,
    reverts_and_reverted,
    ROUND(100.0 * reverts_and_reverted / rev_count, 2) AS percent_reverts_and_reverted
FROM
    pia_final
ORDER BY
    page_namespace,
    rev_count DESC;
SQL

Checking query status...
