knwikisource proofread count

SQL

SELECT 
    u.user_name AS user,  -- Username of the editor
    COUNT(*) AS total_edits,  -- Total number of edits by the user
    SUM(CASE WHEN ctd_name = 'proofreadpage-quality0' THEN 1 ELSE 0 END) AS without_text,
    SUM(CASE WHEN ctd_name = 'proofreadpage-quality1' THEN 1 ELSE 0 END) AS not_proofread,
    SUM(CASE WHEN ctd_name = 'proofreadpage-quality2' THEN 1 ELSE 0 END) AS problematic,
    SUM(CASE WHEN ctd_name = 'proofreadpage-quality3' THEN 1 ELSE 0 END) AS proofread,
    SUM(CASE WHEN ctd_name = 'proofreadpage-quality4' THEN 1 ELSE 0 END) AS validated
FROM 
    revision AS r
    LEFT JOIN change_tag ON (r.rev_id = ct_rev_id)  -- Join to get proofread quality tags
    LEFT JOIN change_tag_def ON (ct_tag_id = ctd_id)  -- Join to get tag definitions
    LEFT JOIN user AS u ON (r.rev_user = u.user_id)  -- Join to get the username (adjust if needed)
WHERE 
    r.rev_timestamp BETWEEN '2024-08-01 00:00:00' AND '2024-09-15 23:59:59'  -- Date range filter
GROUP BY 
    u.user_name
ORDER BY 
    total_edits DESC;
SQL

Checking query status...
