ruwiki: Users with CSS/JS/JSON edits. 29 January 2020

SQL

use ruwiki_p;

SELECT
	user_name,
    IF (ug_group = 'sysop', 'admin', 'engineer') group_name,
    css,
    js,
    json,
    userscripts,
    (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,
    		  		COUNT(*) cnt
				FROM
      				page
	      			STRAIGHT_JOIN revision ON rev_page = page_id
              		STRAIGHT_JOIN actor ON rev_actor = actor_id
				WHERE
    	  			(
                      page_namespace = 8 OR
                      page_namespace = 2 AND
                      page_title NOT LIKE CONCAT(REPLACE(actor_name, ' ', '_'), '%')
                    )
      				AND page_title RLIKE '^.*\.(css|js|json)$'
				GROUP BY
					actor_name,
      				ext,
              		page_namespace
    		) edits
		GROUP BY actor_name
	) edits2
    JOIN user USING (actor_name)
    JOIN user_groups ON (ug_group = 'sysop' OR ug_group = 'engineer') AND ug_user = user_id
ORDER BY
	total DESC
SQL

Checking query status...
