USE arwiki_p;

SET @start_date = '20221201000000';

SET @end_date = '20230101000000';

SET @cumule_Edit=0;

SET @rank=0;

/* SELECT @rank:=@rank+1 AS "المركز", s.* */

SELECT @cumule_Edit:=@cumule_Edit + edit_count AS "تراكم", s.*

FROM (

    SELECT CONCAT("[[مستخدم:",actor_name,"|",actor_name,"]]") AS user_name, SUM(CAST(rev.rev_len as signed)-CAST(parent.rev_len as signed)) AS byte_count, COUNT(rev.rev_id) as edit_count

    FROM revision rev

    INNER JOIN actor on rev.rev_actor = actor_id

    JOIN revision parent

    ON rev.rev_parent_id = parent.rev_id

    INNER JOIN comment_revision on rev.rev_comment_id = comment_id

    JOIN page

    ON page_id = parent.rev_page

    WHERE page_namespace = 0

    and comment_text not like "%رجوع%"

    and comment_text not like "%استرجاع%"

    AND rev.rev_timestamp between @start_date and @end_date

    AND parent.rev_timestamp between @start_date and @end_date

    GROUP BY actor_name

    having byte_count > 0

    ORDER BY byte_count DESC

    LIMIT 100

) s;