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

SQL

SELECT concat('[[مستخدم:', c.comment_text,'|', c.comment_text,']]') AS "المستخدم", COUNT(*) AS "عدد المقالات"
FROM revision r
join page p on p.page_id=r.rev_page
inner join comment c on c.comment_id = r.rev_comment_id
WHERE p.page_namespace=0 and p.page_is_redirect=0 and r.rev_timestamp between 20230729000000 and 20230730000000
	and r.rev_parent_id=0
GROUP BY c.comment_text
ORDER BY COUNT(*) DESC
#LIMIT 100;
/*
select DISTINCT p.page_title,
SUBSTRING_INDEX(SUBSTRING_INDEX(c.comment_text, 'Ibrahem/', -1), ']]', 1),
a.actor_name, r.rev_timestamp, p.page_namespace, r.rev_parent_id
from change_tag t
INNER JOIN change_tag_def ctd on ctd.ctd_id = t.ct_tag_id
INNER JOIN revision r on r.rev_id = t.ct_rev_id
INNER JOIN actor a ON r.rev_actor = a.actor_id
inner join comment c on c.comment_id = r.rev_comment_id
INNER JOIN page p on r.rev_page=p.page_id
where ctd.ctd_name in ("contenttranslation", "contenttranslation-v2") #id = 3 # id = 120
#and r.rev_parent_id = 0
AND r.rev_timestamp > 20210101000000
and comment_text like "%User:Mr. Ibrahem/%"
#and p.page_namespace = 0
group by a.actor_name, c.comment_text*/

SQL

Checking query status...