LLM editor blocks on Enwiki with edit counts by month

SQL

-- Quarry Query: Count block log entries with "LLM" in the comment by month,
--               and sum the edit counts of the blocked registered users.
-- Database: enwiki_p

SELECT
    DATE_FORMAT(log_timestamp, '%Y-%m') AS log_month, -- Format log entry timestamp to YYYY-MM
    COUNT(*) AS block_count, -- Count the number of matching block log entries for registered users
    SUM(user_editcount) AS total_edits_of_blocked_users -- Sum the total edits for those users
FROM
    logging
JOIN
    comment ON log_comment_id = comment_id -- Join to get the comment text for filtering
JOIN
    user ON log_title = user_name -- Join to the user table based on the username logged
WHERE
    log_type = 'block' -- Filter for log entries of type 'block'
    AND log_action IN ('block', 'reblock') -- Include both initial blocks and reblocks actions
    AND comment_text LIKE '%LLM%' -- Filter comments containing the specific string "LLM"
    AND log_namespace = 2 -- IMPORTANT: Only include blocks of registered users (User namespace)
                          -- This is required to join with the user table and get an edit count.
GROUP BY
    log_month -- Group the results by the year and month of the log entry
ORDER BY
    log_month; -- Order the results chronologically
SQL

Checking query status...
