Meta users with extra local autopatrol

SQL

USE metawiki_p;

/* blocked in user groups */
SELECT
	u.user_name,
	ug.*,
    block.ipb_expiry,
	blockreason.comment_text,
	CASE
		WHEN block.ipb_sitewide = 1 THEN 'sitewide'
		ELSE (
			SELECT GROUP_CONCAT(
				CASE
					WHEN restriction.ir_type = 1 THEN (
						SELECT CONCAT( 'ns', page_namespace, '-', page_title ) FROM page WHERE page_id = restriction.ir_value
                    )
					WHEN restriction.ir_type = 2 THEN CONCAT( 'ns', restriction.ir_value, '-all' )
					ELSE 'Something went wrong'
				END
            )
			FROM ipblocks_restrictions restriction
			WHERE restriction.ir_ipb_id = block.ipb_id
        )
	END AS 'Restrictions'
FROM
	user u
JOIN
	user_groups ug
	ON ug.ug_user = u.user_id
JOIN
	ipblocks block
	ON block.ipb_user = u.user_id
JOIN
	comment blockreason
	ON blockreason.comment_id = block.ipb_reason_id
;

SELECT
	u.user_name,
	ug.*,
	other_groups.*
FROM
	user_groups ug
JOIN
	(
		SELECT
			other.ug_user AS user_id,
			GROUP_CONCAT(
				CONCAT(
					other.ug_group,
					CASE
						WHEN other.ug_expiry IS NOT NULL THEN CONCAT('-', other.ug_expiry)
						ELSE ''
					END
    			)
	    	) AS user_groups
		FROM
			user_groups other
		WHERE
			other.ug_group NOT IN ('autopatrolled','ipblock-exempt', 'campaignevents-beta-tester', 'accountcreator')
		GROUP BY other.ug_user
	) AS other_groups
	ON other_groups.user_id = ug.ug_user
JOIN
	user u
	ON u.user_id = ug.ug_user
WHERE
	ug.ug_group = 'autopatrolled'
LIMIT 1000;
SQL

Checking query status...
