-- 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