Wikimedia incubator stats

SQL

WITH base_table AS (
  SELECT DISTINCT rev_id, rev_actor, rev_timestamp, rev_len, rev_parent_id,
  page_namespace, page_id, actor_id,
  DAY(rev_timestamp), MONTH(rev_timestamp), YEAR(rev_timestamp),
  REGEXP_SUBSTR(page_title, 'W[a-z]/[a-z]+') AS prefix,
  rc_old_len - rc_new_len AS byte_diff
  FROM revision rev
  JOIN page ON rev.rev_page = page.page_id
  JOIN actor ON rev.rev_actor = actor.actor_id
  JOIN recentchanges rc ON rev.rev_id = rc.rc_this_oldid
  WHERE page_namespace IN (0, 1, 10, 11, 14, 15, 828, 829)
  HAVING
      prefix <> ''
      AND byte_diff IS NOT NULL
  ORDER BY rev_id DESC),
  
edits_table AS (
SELECT prefix, COUNT(DISTINCT rev_id) AS edit_count
FROM base_table
GROUP BY prefix
),

actor_table AS (
SELECT prefix, COUNT(DISTINCT actor_id) AS actor_count
FROM base_table
GROUP BY prefix
),

byte_table AS (
SELECT prefix, 
    SUM(CASE WHEN byte_diff < 0 THEN byte_diff ELSE 0 END) AS bytes_removed,
    SUM(CASE WHEN byte_diff >= 0 THEN byte_diff ELSE 0 END) AS bytes_added
FROM base_table
GROUP BY prefix
),

page_table AS (
SELECT prefix, COUNT(DISTINCT page_id) AS pages_count
FROM base_table
GROUP BY prefix
)

SELECT e.prefix, edit_count, actor_count, bytes_added, bytes_removed, pages_count
FROM edits_table e
JOIN actor_table a ON a.prefix = e.prefix
JOIN byte_table b  ON b.prefix = e.prefix
JOIN page_table p ON p.prefix = e.prefix
ORDER BY p.pages_count DESC
SQL

Checking query status...
