Average, maximum, minimum edit size per user with 200 edits or more in English Wiktionary

Fork of https://quarry.wmflabs.org/query/42047. Shows average, maximum, minimum edit size (difference in byte length of wikitext between current edit and previous edit), and total number of edits, in the 1,000,000 most recent edits. Shows only users with 200 or more edits in the sample.

SQL

use enwiktionary_p;

select
	actor_name as user,
    avg(diff) as average,
    max(diff) as maximum,
    min(diff) as minimum,
    count(*) as count
from (select
      	cast(after.rev_len as signed)
      		- if(before.rev_len is null, 0, cast(before.rev_len as signed)) -- If before.rev_len is null, this is a page creation.
      		as diff,
        actor_name
      from revision as after
      left join revision as `before`
      	on before.rev_id = after.rev_parent_id
      join actor on actor_id = after.rev_actor
      order by after.rev_timestamp desc
      limit 1000000) -- total number of recent edits to examine
      as diffs
-- where actor_user is not null -- Uncomment this to remove IP users.
group by actor_name
having count >= 200 -- minimum number of edits
order by average desc;

SQL

Checking query status...