V general - Quarry

SQL

-- общий
SELECT REGEXP_REPLACE(comment_text, '.*?u:(.*?)\\|.*', '\\1') AS username, COUNT(*) AS actions 
FROM logging
JOIN actor ON actor_id = log_actor
JOIN comment ON comment_id = log_comment_id
WHERE log_timestamp > "20220101000000"
AND actor_name = "QBA-bot"
AND (log_type = "block" OR log_type = "protect")
AND (comment_text LIKE "%блокировка ботом%" OR comment_text LIKE "%защиты ботом%")
GROUP BY username
ORDER BY actions DESC;

-- блокировки
SELECT REGEXP_REPLACE(comment_text, '.*?u:(.*?)\\|.*', '\\1') AS username, COUNT(*) AS blocks 
FROM logging
JOIN actor ON actor_id = log_actor
JOIN comment ON comment_id = log_comment_id
WHERE log_timestamp > "20220101000000"
AND actor_name = "QBA-bot"
AND (log_type = "block" OR log_type = "protect")
AND comment_text LIKE "%блокировка ботом%"
GROUP BY username
ORDER BY blocks DESC;

-- защиты
SELECT REGEXP_REPLACE(comment_text, '.*?u:(.*?)\\|.*', '\\1') AS username, COUNT(*) AS protections 
FROM logging
JOIN actor ON actor_id = log_actor
JOIN comment ON comment_id = log_comment_id
WHERE log_timestamp > "20220101000000"
AND actor_name = "QBA-bot"
AND (log_type = "block" OR log_type = "protect")
AND comment_text LIKE "%защиты ботом%"
GROUP BY username
ORDER BY protections DESC;
SQL

Checking query status...
