Fish articles by Lumpsucker

This query produces a list of small Olympian biographies that are created and primarily contain content by Sander.v.Ginkel. This is currently defined as articles with a page length under 4000 with Sander.v.Ginkel contributing more than half the content, but can be adjusted. In addition, redirects, lists and disambiguation pages are excluded.

SQL

SELECT COUNT(*) articles,
	SUM(CASE WHEN rev_len < 2500 THEN 1 ELSE 0 END) as smallCreations, 
    SUM(CASE WHEN rev_len < 2500 AND page_len < 3000 AND page_is_redirect = 0 THEN 1 ELSE 0 END) as smallCurrent,
    SUM(page_is_redirect) as redirectCurrent,
    TRUNCATE(rev_timestamp,-8)/100000000 as month
FROM (
  SELECT DISTINCT page_id, rev_len, page_len, page_is_redirect, rev_timestamp
  FROM (
    SELECT *
    FROM revision
    JOIN actor_revision ON actor_id = rev_actor
    JOIN page ON page_id = rev_page
    WHERE rev_parent_id = 0
      AND actor_name = "Lumpsucker"
      AND page_namespace = 0
      AND rev_len > 150)
    AS noncategory
  JOIN categorylinks ON cl_from = page_id
  WHERE cl_to LIKE '%fish%' OR cl_to LIKE '%Fish%')
  AS category
GROUP BY month
ORDER BY month DESC

SQL

Checking query status...