Untitled query #4680

SQL

USE plwiktionary_p;

-- Resultset 1

SELECT
	IFNULL(cl_to, "Total") AS category,
	COUNT(DISTINCT page_title) AS unflagged
FROM
	page JOIN categorylinks ON
		cl_from = page_id AND
		cl_to IN ("importSV", "importIA", "jidyszbot", "importCStematyczny")
WHERE
	page_namespace = 0 AND
	NOT EXISTS (SELECT NULL FROM flaggedpages WHERE fp_page_id = page_id)
GROUP BY
	cl_to WITH ROLLUP;

-- Resultset 2

SELECT
	IFNULL(cl_to, "Total") AS category,
	COUNT(DISTINCT page_title) AS unflagged
FROM
	page
		JOIN categorylinks ON
			cl_from = page_id AND
			cl_to IN ("importSV", "importIA", "jidyszbot", "importCStematyczny")
		JOIN revision r ON
			r.rev_page = page_id
WHERE
	page_namespace = 0 AND
	NOT EXISTS (SELECT NULL FROM flaggedpages WHERE fp_page_id = page_id) AND
	EXISTS (
		SELECT
			NULL
		FROM
			page p2 JOIN revision r2 ON
				r2.rev_page = p2.page_id
		WHERE
			r2.rev_page = r.rev_page
		GROUP BY
			p2.page_title
		HAVING
			MAX(r2.rev_user_text NOT IN (
				SELECT
					user_name
				FROM
					user_groups JOIN user ON
						user_id = ug_user
				WHERE
					ug_group = "bot"
			) AND r2.rev_user_text NOT IN (
				"Interwicket", "Conversion script", "Template namespace initialisation script", "CommonsDelinker"
			)) = 0
	)
GROUP BY
	cl_to WITH ROLLUP;

-- Resultset 3

SELECT
	DISTINCT page_title,
	cl_to AS category,
	page_latest
FROM
	page
		JOIN categorylinks ON
			cl_from = page_id AND
			cl_to IN ("importSV", "importIA", "jidyszbot", "importCStematyczny")
		JOIN revision ON
			rev_page = page_id
WHERE
	page_namespace = 0 AND
	NOT EXISTS (SELECT NULL FROM flaggedpages WHERE fp_page_id = page_id)
GROUP BY
	page_title
HAVING
	MAX(rev_user_text NOT IN (
		SELECT
			user_name
		FROM
			user_groups JOIN user ON
				user_id = ug_user
		WHERE
			ug_group = "bot"
    ) AND rev_user_text NOT IN (
		"Interwicket", "Conversion script", "Template namespace initialisation script", "CommonsDelinker"
    )) = 0;
SQL

Checking query status...
