WP:NOTWEBHOST - Quarry

SQL

SELECT
	*,
    ((computed.userspace_contribs - sandbox_contribs) / computed.recent_contribs) AS contrib_ratio
FROM (
    SELECT
        raw.actor_name,
        COUNT(raw.rc_actor) AS recent_contribs,
  		SUM(raw.abs_diff_size) AS recent_abs_diff,
        SUM(raw.in_userspace) AS userspace_contribs,
		SUM(raw.abs_diff_size) OVER(PARTITION BY raw.rc_actor, raw.in_userspace) AS recent_abs_diff_userspace,
        SUM(raw.in_sandbox) AS 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.*,
            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_title LIKE '%/sandbox%' THEN 1
      			ELSE 0
      		END AS in_sandbox
        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
      		# Limit to root pages only
      		AND NOT recentchanges_userindex.rc_title LIKE '%/%'
      		# 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 recent_contribs > 15
  		AND recent_contribs - userspace_contribs + sandbox_contribs < 5
) as computed
HAVING contrib_ratio > 0.8
ORDER BY contrib_ratio DESC, userspace_contribs DESC
LIMIT 500;
SQL

Checking query status...
