Active users in last 30 days in fawp + their editing tendencies

SQL

use fawiki_p;
SELECT
  rev_user_text,
  SUM(freq) TotalEdits,
  SUM(
    CASE
      WHEN page_namespace = 0 THEN freq
      ELSE 0
    END
  ) / SUM(freq) ContentEdits
FROM (
  SELECT
    rev_user_text,
    page_namespace,
    COUNT(*) freq
  FROM revision
  JOIN page
    ON rev_page = page_id
  WHERE rev_user IN
  (
    SELECT rev_user
    FROM revision
    JOIN user_groups
      ON rev_user = ug_user
      AND ug_group = 'bot'
    WHERE
      rev_timestamp > DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y%m%d000000')
      AND rev_user <> 0
      AND ug_user IS NULL
  )
  GROUP BY
    rev_user,
    page_namespace
) last30days
GROUP BY rev_user_text

SQL

Checking query status...