USE elwiki_p;

SELECT CONCAT(month, '-', year) AS monthly, SUM(period < 7) AS less_than_1_week, SUM(period BETWEEN 7 AND 30) AS 1_week_to_1_month, SUM(period BETWEEN 31 AND 365) AS 1_month_to_1year, SUM(period > 365) AS more_than_1_year, SUM(period > 3650) AS more_than_10_year

FROM (

SELECT SUBSTR(first, 5, 2) AS month, SUBSTR(first, 1, 4) AS year, TIMESTAMPDIFF(DAY, first, last) AS period

FROM (

SELECT MIN(rev_timestamp) AS first, MAX(rev_timestamp) AS last

FROM revision_userindex

WHERE rev_user != 0 AND rev_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot')

GROUP BY rev_user

) a

) b

GROUP BY monthly

ORDER BY year, month;