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

Fork of Distribution of registration time for users with 5+ edits (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,
    SUBSTR(user_registration, 1, 6) 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 >= 5
) tmp
GROUP BY registration_date
ORDER BY registration_date
;

SQL

Checking query status...