Registrations of users who made 100+ edits in their first month (huwiki)

Fork of Registrations of users who made 5+ edits in their first month (huwiki) by Tgr

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

SQL

use huwiki_p;

SELECT
	-- CONCAT(SUBSTR(user_registration, 1, 4), 'q', (SUBSTR(user_registration, 5, 2) - 1) DIV 3 + 1) AS registration_date,
    CONCAT(SUBSTR(user_registration, 1, 4), '-', SUBSTR(user_registration, 5, 2)) AS registration_date,
    count(*) AS new_users
FROM (
	SELECT
		user_id,
		user_registration,
		count(*) AS recent_edits
	FROM
		user
		JOIN actor ON user_id = actor_user
		JOIN revision ON actor_id = rev_actor AND rev_timestamp BETWEEN user_registration AND DATE_FORMAT(STR_TO_DATE(user_registration, '%Y%m%d%H%i%s') + INTERVAL 30 DAY, '%Y%m%d%H%i%s')
	WHERE user_registration IS NOT NULL
	GROUP BY user_id, user_registration
	HAVING recent_edits >= 100
) tmp
GROUP BY registration_date
ORDER BY registration_date
;

SQL

Checking query status...