USE wikidatawiki_p;

SELECT rev1.rev_user_text AS user_name, /*IFNULL(GROUP_CONCAT(DISTINCT ug_group),' ') AS user_group,*/

    COUNT(rev1.rev_id) AS number, SUM(rev1.rev_len) - SUM(rev2.rev_len) AS data_amount,

    (SUM(rev1.rev_len) - SUM(rev2.rev_len)) / COUNT(rev1.rev_id) AS ratio

    FROM (SELECT * FROM revision WHERE rev_comment LIKE '%wbmergeitems-from%') AS rev1

    JOIN revision AS rev2 ON rev2.rev_id = rev1.rev_parent_id

    #LEFT JOIN user_groups ON rev1.rev_user IS NOT NULL AND ug_user = rev1.rev_user

WHERE rev1.rev_len IS NOT NULL AND rev2.rev_len IS NOT NULL

GROUP BY rev1.rev_user_text HAVING COUNT(rev1.rev_id) > 50

ORDER BY number DESC, data_amount DESC, user_name;