Top writers NL-wiki, 2024

SQL

SELECT COUNT(1) AS pages_with_disambig_but_not_redir FROM page
WHERE (page_namespace = 0 AND page_is_redirect = 0);
SELECT
	CASE WHEN user_id_ IS NULL THEN -1 WHEN user_articles >= 10000 THEN user_id_ ELSE -2 END AS user_id,
	CASE WHEN user_articles >= 150 THEN user_name ELSE CONCAT("<", COUNT(1), " registered users under the threshold>") END AS user_name,
	CASE WHEN user_articles >= 150 THEN user_articles ELSE SUM(user_articles) END AS user_articles,
	CASE WHEN user_articles >= 150 THEN user_latestcreation ELSE MAX(user_latestcreation) END AS user_latestcreation
FROM (
	SELECT
		actor_user AS user_id_,
		CASE
			WHEN actor_user IS NULL THEN CONCAT("<", COUNT(DISTINCT actor_id), " unregistered users>")
			ELSE actor_name
		END AS user_name,
		COUNT(1) AS user_articles,
		MAX(rev_timestamp) AS user_latestcreation
	FROM (
		SELECT rev_actor, MIN(rev_timestamp) AS rev_timestamp FROM revision
		LEFT JOIN page ON revision.rev_page = page.page_id
		WHERE (page_namespace = 0 AND page_is_redirect = 0)
		GROUP BY revision.rev_page
	) AS uti
	LEFT JOIN actor ON uti.rev_actor = actor.actor_id
	GROUP BY user_id_
) AS uti_
GROUP BY user_id
ORDER BY user_articles DESC;
SQL

Checking query status...
