Bot-to-bot time to revert in English Wikipedia articles

Fork of Bot-to-bot reverts in English Wikipedia by Namespace by EpochFail

This query is marked as a draft This query has been published by EpochFail.

SQL

SELECT
	IF(TIMESTAMPDIFF(HOUR, reverted.rev_timestamp, reverted.reverting_timestamp) < 1, "1. < hour",
       IF(TIMESTAMPDIFF(DAY, reverted.rev_timestamp, reverted.reverting_timestamp) < 1, "2. hour-day",
       IF(TIMESTAMPDIFF(WEEK, reverted.rev_timestamp, reverted.reverting_timestamp) < 1, "3. day-week",
       IF(TIMESTAMPDIFF(MONTH, reverted.rev_timestamp, reverted.reverting_timestamp) < 1, "4. week-month",
       IF(TIMESTAMPDIFF(YEAR, reverted.rev_timestamp, reverted.reverting_timestamp) < 1, "5. month-year", "6. > year"))))) AS time_to_revert_block,
    COUNT(*),
    COUNT(DISTINCT rev_user),
    COUNT(DISTINCT reverting_user)
FROM u2041__botvbot_p.enwiki_bot2bot_reverted_20140820 AS reverted
WHERE page_namespace = 0
GROUP BY 1
ORDER BY 1;

SQL

Checking query status...