Get number of contribs, number of revisions per contrib, and percentage of contribution for those contribs using last 3 months activity to identify contribs.

core_contrib_analysis.sql

SELECT count(username) AS num_contribs,
	group_concat(username, ' (', user_total_revisions, ' revisions)' separator ', ') AS contribs,
	sum(user_total_revisions) AS revisions_by_these_contribs,
	overall_total_revisions,
	(sum(user_total_revisions) / overall_total_revisions * 100) AS percentage_revisions_by_these_contribs

FROM (
	/* get usernames WITH X revisions IN ANY month OF the last 3, AND Y revisions overall IN the last 3 */
	SELECT all_revisions.count_of_revisions AS overall_total_revisions,
		revisions_per_user.username,
		revisions_per_user.count_of_revisions AS user_total_revisions,
		revisions_per_user.count_of_revisions,
		max(monthly_revision_count.count_of_revisions) AS max_monthly_revisions
	
	FROM
	
		/* get total revisions per user OVER the past 3 months WHERE min overall IS X */
		(
		SELECT count(wr.id) AS count_of_revisions,
			au.username
		
		FROM wiki_revision wr,
			auth_user au
		
		WHERE au.id = wr.creator_id
			AND wr.created > date_sub(curdate(), interval 3 month)
			AND wr.creator_id NOT IN (
				SELECT user_id
				
				FROM users_userban
				)
		
		GROUP BY creator_id
		
		HAVING count_of_revisions >= 75 /* overall equals this */
		
		ORDER BY count_of_revisions DESC
		) AS revisions_per_user,
		
		/* get revisons per user per month FOR past 3 months WHERE at least one month had Y */
		(
		SELECT count(wr.id) AS count_of_revisions,
			date_format(wr.created, '%Y-%m') AS yearmonth_created,
			au.username
		
		FROM wiki_revision wr,
			auth_user au
		
		WHERE au.id = wr.creator_id
			AND wr.created > date_sub(curdate(), interval 3 /* this many months ago */ month)
			AND wr.creator_id NOT IN (
				SELECT user_id
				
				FROM users_userban
				)
		
		GROUP BY yearmonth_created,
			creator_id
		
		HAVING count_of_revisions >= 50 /* at least one month has this much */
		
		ORDER BY count_of_revisions DESC
		) AS monthly_revision_count,
		
		/* get total revisons FOR ALL users IN past 3 months */
		(
		SELECT count(wr.id) AS count_of_revisions,
			date_format(wr.created, '%Y-%m') AS yearmonth_created,
			au.username
		
		FROM wiki_revision wr,
			auth_user au
		
		WHERE au.id = wr.creator_id
			AND wr.created > date_sub(curdate(), interval 3 /* this many months ago */ month)
			AND wr.creator_id NOT IN (
				SELECT user_id
				
				FROM users_userban
				)
		) AS all_revisions

	WHERE monthly_revision_count.username = revisions_per_user.username
	
	GROUP BY username
	
	ORDER BY user_total_revisions DESC
	) AS user_summary_data