Contributions to level 1-2 vital articles

SQL

WITH vital AS
(
  SELECT Pa.page_id , Pa.page_title, rev_id, actor_name, rev_len,
  LEAD(rev_len) OVER (ORDER BY Pa.page_id, rev_id) AS next_len, LEAD(Pa.page_id) OVER (ORDER BY Pa.page_id, rev_id) AS next_id,
  LEAD(rev_len, 2) OVER (ORDER BY Pa.page_id, rev_id) AS next_len2, LEAD(Pa.page_id, 2) OVER (ORDER BY Pa.page_id, rev_id) AS next_id2,
  LAG(rev_len) OVER (ORDER BY Pa.page_id, rev_id) AS prev_len, LAG(Pa.page_id) OVER (ORDER BY Pa.page_id, rev_id) AS prev_id,
  LAG(rev_len, 2) OVER (ORDER BY Pa.page_id, rev_id) AS prev_len2, LAG(Pa.page_id, 2) OVER (ORDER BY Pa.page_id, rev_id) AS prev_id2
  LAG(rev_len, 3) OVER (ORDER BY Pa.page_id, rev_id) AS prev_len3, LAG(Pa.page_id, 2) OVER (ORDER BY Pa.page_id, rev_id) AS prev_id3
  FROM categorylinks
  JOIN page Pt ON Pt.page_id=cl_from AND Pt.page_namespace=1
  JOIN page Pa ON Pa.page_namespace=0 AND Pa.page_title=Pt.page_title
  JOIN revision R ON rev_page=Pa.page_id
  JOIN actor ON actor_id=rev_actor
  WHERE cl_to = "All_Wikipedia_level-1_vital_articles"
)
SELECT actor_name, SUM(CASE WHEN page_id != prev_id OR prev_id IS NULL THEN rev_len /* New article */
                            WHEN rev_len > prev_len THEN rev_len - prev_len /* Growth */
                            ELSE 0 END) AS bytes
FROM vital
WHERE NOT (page_id = prev_id2 AND rev_len = prev_len2) /* This is a revert */
AND NOT (page_id = prev_id3 AND rev_len = prev_len3) /* This is a revert of two edits */
AND NOT (next_id = prev_id AND next_len = prev_len) /* This is reverted */
AND NOT (next_id = prev_id2 AND next_len = prev_len2) /* This and the previous edit are reverted */
AND NOT (next_id2 = prev_id AND next_len2 = prev_len) /* This and the next edit are reverted */
GROUP BY actor_name
ORDER BY bytes DESC
SQL

Checking query status...
