knwikisource proofreadathon edit count

SQL

USE knwikisource_p;

SELECT
    CONCAT("# [[Special:Contributions/", actor_name, "]]") AS rev_user_text,
    COUNT(DISTINCT revision.rev_id) AS recent_user_editcount,
    SUM(IF(ctd_name = 'proofreadpage-quality0', 1, 0)) AS without_text,
    SUM(IF(ctd_name = 'proofreadpage-quality1', 1, 0)) AS not_proofread,
    SUM(IF(ctd_name = 'proofreadpage-quality2', 1, 0)) AS problematic,
    SUM(IF(ctd_name = 'proofreadpage-quality3', 1, 0)) AS proofread,
    SUM(IF(ctd_name = 'proofreadpage-quality4', 1, 0)) AS validated,
    COUNT(*) AS total_rev_count
FROM
    revision
    JOIN actor ON rev_actor = actor_id
    JOIN page ON rev_page = page_id
    LEFT JOIN change_tag ON rev_id = ct_rev_id
    LEFT JOIN change_tag_def ON ctd_id = ct_tag_id
WHERE
    actor_user IS NOT NULL
    AND page_namespace IN ('0', '104', '106')
    AND rev_timestamp BETWEEN '2024-07-01 00:00:00' AND '2024-12-31 23:59:59'
    AND (ctd_name LIKE 'proofreadpage-quality%' OR ctd_name IS NULL)  -- Include cases where no proofread data exists
GROUP BY
    actor_id
ORDER BY
    recent_user_editcount DESC
LIMIT 100;
SQL

Checking query status...
