block-query.sql

SELECT
    %s AS wiki,
    log_user_text AS user,
    (
        SELECT IF (COUNT(*) > 0, 'No', 'Yes')
        FROM user_properties
        WHERE up_property = 'disablemail'
        AND up_value = 1 AND up_user = log_user
    ) AS disablemail,
    COUNT(*) AS total_blocks
FROM logging
LEFT JOIN user_groups ON ug_user = log_user
    AND ug_group IN ('bot', 'flow-bot')
WHERE log_action = 'block' AND log_type = 'block'
AND log_timestamp >= 20170801000000 AND log_timestamp <= 20171031235959
AND ug_group IS NULL
GROUP BY log_user ORDER BY total_blocks DESC LIMIT 15