<deleted> - Quarry

SQL

use ruwiki_p;

SELECT
	IF (ug_group = 'sysop', 'admin', 'engineer') group_name,
    SUM(css),
    SUM(js),
    SUM(json),
    SUM(userscripts),
    SUM(css + js + json + userscripts) total
FROM
	(
		SELECT
			user_name,
			SUM(CASE WHEN ext = 'css' AND page_namespace = 8 THEN cnt ELSE 0 END) css,
			SUM(CASE WHEN ext = 'js' AND page_namespace = 8 THEN cnt ELSE 0 END) js,
			SUM(CASE WHEN ext = 'json' AND page_namespace = 8 THEN cnt ELSE 0 END) json,
      		SUM(CASE WHEN page_namespace = 2 THEN cnt ELSE 0 END) userscripts
		FROM
      		(
				SELECT
              		page_namespace,
      				substr(regexp_substr(page_title, '\.(css|js|json)$'), 2) ext,
		      		rev_user_text user_name,
    		  		COUNT(*) cnt
				FROM
      				page
	      			STRAIGHT_JOIN revision ON rev_page = page_id
				WHERE
    	  			(
                      page_namespace = 8 OR
                      page_namespace = 2 AND
                      page_title NOT LIKE CONCAT(REPLACE(rev_user_text, ' ', '_'), '%')
                    )
      				AND page_title RLIKE '^.*\.(css|js|json)$'
				GROUP BY
					user_name,
      				ext,
              		page_namespace
    		) edits
		GROUP BY user_name
	) edits2
    JOIN user USING (user_name)
    JOIN user_groups ON (ug_group = 'sysop' OR ug_group = 'engineer') AND ug_user = user_id
GROUP BY
	group_name
ORDER BY
	total DESC
SQL

Checking query status...
