أكثر المستخدمين إنشاءً للمقالات ضمن مشروع ويكي طب

SQL

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 20240101000000 AND 20241231235959
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 20240101000000 AND 20241231235959
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;
SQL

Checking query status...
