WP:NOTWEBHOST - Quarry

SQL

SELECT
	*,
    ((computed.usrspc_contribs - sndbx_contribs) / computed.contribs) AS contrib_ratio
FROM (
    SELECT
        raw.actor_name,
        COUNT(raw.rc_actor) AS contribs, # recent_contribs
  		SUM(raw.abs_diff_size) AS abs_diff, # recent_abs_diff
        SUM(raw.in_userspace) AS usrspc_contribs, # userspace_contribs
        SUM(raw.in_own_userspace) AS own_usrspc_contribs, # own_userspace_contribs
		#SUM(raw.abs_diff_size) OVER(PARTITION BY raw.rc_actor, raw.in_userspace) AS recent_abs_diff_userspace,
        SUM(raw.in_subpage) AS sbpg_contribs, # subpace_contribs
        SUM(raw.in_sandbox) AS sndbx_contribs # sandbox_contribs
		#SUM(raw.abs_diff_size) OVER(PARTITION BY raw.rc_actor, raw.in_sandbox) AS recent_abs_diff_sandbox
    FROM (
        SELECT
      		recentchanges_userindex.rc_title,
            recentchanges_userindex.rc_actor,
            actor.actor_name,
      		ABS(recentchanges_userindex.rc_new_len - recentchanges_userindex.rc_old_len) AS abs_diff_size,
            CASE
                WHEN recentchanges_userindex.rc_namespace IN (2,3) THEN 1
                ELSE 0
            END AS in_userspace,
            CASE
                WHEN recentchanges_userindex.rc_namespace IN (2,3)
      				AND recentchanges_userindex.rc_title LIKE
                    CONCAT('%', REPLACE(actor.actor_name, ' ', '_'), '%') THEN 1
                ELSE 0
            END AS in_own_userspace,
      		CASE
      			WHEN recentchanges_userindex.rc_title LIKE '%/%' THEN 1
      			ELSE 0
      		END AS in_subpage,
      		CASE
      			WHEN recentchanges_userindex.rc_title LIKE '%/sandbox%' THEN 1
      			ELSE 0
      		END AS in_sandbox,
      		CASE
      			WHEN false
      				# Identified student group, updated 20220630
      				OR (recentchanges_userindex.rc_namespace = 2 AND recentchanges_userindex.rc_title LIKE '%OberMegaTrans%')
      				THEN 1
      			ELSE 0
      		END AS exclusion_condition
        FROM recentchanges_userindex
            JOIN actor ON recentchanges_userindex.rc_actor = actor.actor_id
      		JOIN comment ON comment.comment_id = recentchanges_userindex.rc_comment_id
        WHERE recentchanges_userindex.rc_type IN (0,1)
            AND recentchanges_userindex.rc_bot = 0
      		# Filter Wikipedia Adventure
      		AND NOT (recentchanges_userindex.rc_namespace = 2 AND comment.comment_text LIKE '%automatically%')
      		AND NOT recentchanges_userindex.rc_title LIKE '%TWA/Earth%'
      		# Filter "recently" blocked
      		AND actor.actor_name NOT IN (SELECT recentchanges.rc_title
                                         FROM recentchanges
                                         WHERE recentchanges.rc_type = 3
                                             AND recentchanges.rc_log_action = 'block')
    ) as raw
  	GROUP BY raw.rc_actor
  	HAVING true
  		AND contribs > 15
  		AND contribs - usrspc_contribs + sndbx_contribs < 5
  		AND abs_diff > 1000
		AND SUM(raw.exclusion_condition) = 0
) as computed
HAVING contrib_ratio > 0.8
ORDER BY contrib_ratio DESC, (usrspc_contribs - sbpg_contribs) DESC
LIMIT 500;
SQL

Checking query status...
