Wikimaniacs of fawiki

SQL

USE fawiki_p;
SELECT
  actor_name,
  COUNT(*) total,
  SUM(CASE WHEN page_namespace = 0 THEN 1 ELSE 0 END) / COUNT(*) AS article_pct
FROM actor_revision
JOIN revision_userindex
  ON actor_id = rev_actor
JOIN page
  ON rev_page = page_id
WHERE
  rev_timestamp > DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 180 DAY), '%Y%m01000000')
  AND rev_timestamp < DATE_FORMAT(NOW(), '%Y%m01000000')
  AND actor_user NOT IN (
    SELECT ug_user
    FROM user_groups
    WHERE ug_group = 'bot'
  )
  AND actor_name NOT IN (
    'پیام به کاربر جدید',
    'Community Tech bot',
    'FawikiPatroller',
    'SteinsplitterBot'
  )
  AND page_title NOT LIKE '%صفحه_تمرین'
GROUP BY actor_name
HAVING
 COUNT(*) > 360
 AND SUM(CASE WHEN page_namespace = 0 THEN 1 ELSE 0 END) / COUNT(*) < 0.3
ORDER BY total DESC
SQL

Checking query status...
