use arwiki_p;

# by actor_name in actor table 

# https://www.mediawiki.org/wiki/Manual:Actor_table

select actor_name,actor_user, count(actor_name)

FROM revision

JOIN page ON rev_page = page_id

join actor on actor_id = rev_actor

WHERE page_namespace = 0

AND rev_parent_id = 0

and rev_timestamp BETWEEN 20250101000000 AND 20251231235959

AND page.page_is_redirect = 0

AND page.page_id in (select cl_from from categorylinks where cl_from = page.page_id and cl_to = "بوابة_طب/مقالات_متعلقة")

group by actor_name

ORDER BY count(actor_name) desc;

​

# by user_name in user table

# https://www.mediawiki.org/wiki/Manual:User_table

select user_name,user_id, count(user_name)

FROM revision

JOIN page ON rev_page = page_id

join actor on actor_id = rev_actor

join user on user_id = actor_user

WHERE page_namespace = 0

AND rev_parent_id = 0

and rev_timestamp BETWEEN 20250101000000 AND 20251231235959

AND page.page_is_redirect = 0

AND page.page_id in (select cl_from from categorylinks where cl_from = page.page_id and cl_to = "بوابة_طب/مقالات_متعلقة")

group by user_name

ORDER BY count(user_name) desc;