(test) rowiki users by no. of articles created

Testing a version that uses distinct column names, since the usual one suddenly started freaking out.
Also, the results have been wrong since late February 2022 and I'm trying to investigate why.

SQL

USE rowiki_p;
SELECT rev_actor AS actor_id, actor_user, actor_name, COUNT(rev_page) AS actor_createdpages FROM (
	SELECT rev_id, rev_page, rev_actor, actor_user, actor_name
	FROM (
		SELECT MAX(rev_id) AS rev_id, rev_page, rev_actor FROM revision
		WHERE rev_parent_id = 0
		GROUP BY rev_page
	) AS revi
	CROSS JOIN (
		SELECT page_id FROM page
		WHERE (page_namespace = 0 AND page_is_redirect = 0)
	) AS pge ON revi.rev_page = pge.page_id
	LEFT JOIN (
		SELECT actor_id, actor_user, actor_name FROM actor
	) AS act ON revi.rev_actor = act.actor_id
) AS actt
GROUP BY rev_actor
ORDER BY actor_createdpages DESC
LIMIT 10;

SQL

Checking query status...