ويكيبيديا:إحصاءات العام

SELECT COUNT(user_name) FROM user  WHERE user_registration BETWEEN 20190101000000 AND 20191231235959; 

SET @rank=0; SELECT @rank:=@rank+1 AS "المركز", s.* FROM (   SELECT CONCAT("[[مستخدم:",rev_user_text,"|",rev_user_text,"]]") AS "المستخدم", COUNT(rev_user_text) AS "عدد التعديلات" FROM revision  WHERE rev_timestamp BETWEEN 20190101000000 AND 20181231235959 AND rev_user_text NOT LIKE ucase ("%BOT") COLLATE utf8_general_ci AND rev_user_text NOT LIKE ("%New_user_message") GROUP BY rev_user_text ORDER BY COUNT(rev_user_text) DESC LIMIT 10) s; 

SELECT  COUNT(*) AS "عدد المقالات" FROM revision r join page p on p.page_id=r.rev_page WHERE p.page_namespace=0 and p.page_is_redirect=0 and r.rev_timestamp between 20190101000000 and 20191231235959 and r.rev_parent_id=0 ORDER BY COUNT(*) DESC   LIMIT 1; 

SELECT  COUNT(*) AS "عدد القوالب" FROM revision r join page p on p.page_id=r.rev_page WHERE p.page_namespace=10 and p.page_is_redirect=0 and r.rev_timestamp between 20190101000000 and 20191231235959 and r.rev_parent_id=0 ORDER BY COUNT(*) DESC   LIMIT 1; 

SELECT  COUNT(*) AS "عدد التصانيف" FROM revision r join page p on p.page_id=r.rev_page WHERE p.page_namespace=14 and p.page_is_redirect=0 and r.rev_timestamp between 20190101000000 and 20191231235959 and r.rev_parent_id=0 ORDER BY COUNT(*) DESC   LIMIT 1; 

 select COUNT(*) from logging where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action = "upload" 

select count(*) from revision where rev_timestamp between 20190101000000 AND 20191231235959; 

 select COUNT(*) from logging where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action = "move" 

 select COUNT(*) from logging where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action = "delete" 

 select COUNT(*) from logging where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action = "protect" 

 select COUNT(*) from logging where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action = "block" 

 select COUNT(*) from logging where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action = "rights" 

 select COUNT(*) from logging where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action = "thanks" 

select count(*) from revision where rev_user_text regexp '^([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])\\.([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])\\.([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])\\.([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])$' and rev_timestamp between 20190101000000 AND 20191231235959; 

SELECT   page_namespace,   page_title,   COUNT(*) FROM revision JOIN page ON page_id = rev_page WHERE rev_timestamp BETWEEN 20190101000000 AND 20191231235959 GROUP BY page_namespace, page_title ORDER BY COUNT(*) DESC, page_title ASC LIMIT 10; 

SELECT CONCAT("[[مستخدم:",user_name,"|",user_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 user JOIN revision rev ON user_id = rev.rev_user JOIN revision parent ON rev.rev_parent_id = parent.rev_id JOIN page ON page_id = parent.rev_page WHERE page_namespace = 0 and rev.rev_comment not like "%رجوع%" and rev.rev_comment not like "%استرجاع%" AND rev.rev_timestamp BETWEEN 20190101000000 AND 20191231235959 AND parent.rev_timestamp BETWEEN 20190101000000 AND 20191231235959 GROUP BY user_name having byte_count > 0 ORDER BY byte_count DESC LIMIT 10; 

select CONCAT('[[مستخدم:',user_name,'|',user_name,']]'), COUNT(*) from logging inner join user on user.user_id = logging.log_user where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action IN ("move"/*, "block", "protect", "rights"*/) group by logging.log_user having COUNT(*)>1 ORDER BY COUNT(*) DESC LIMIT 5; 

select CONCAT('[[مستخدم:',user_name,'|',user_name,']]'), COUNT(*) from logging inner join user on user.user_id = logging.log_user where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action IN ("thanks"/*, "block", "protect", "rights"*/) group by logging.log_user having COUNT(*)>1 ORDER BY COUNT(*) DESC LIMIT 5; 

SELECT rev_user_text, count(rev_user_text) FROM revision INNER JOIN page ON rev_page = page_id WHERE page_namespace = 0 AND rev_parent_id = 0 and rev_timestamp BETWEEN 20190101000000 AND 20191231235959 AND page.page_is_redirect = 0 group by rev_user_text ORDER BY count(rev_user_text) desc; 

select CONCAT('[[مستخدم:',user_name,'|',user_name,']]'), COUNT(*) from logging inner join user on user.user_id = logging.log_user where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action = "approve" and log_namespace = 0 group by logging.log_user having COUNT(*)>1 ORDER BY COUNT(*) DESC LIMIT 1; 

select CONCAT('[[مستخدم:',user_name,'|',user_name,']]'), COUNT(*) from logging inner join user on user.user_id = logging.log_user where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_action = "approve-i" and log_namespace = 0 group by logging.log_user having COUNT(*)>1 ORDER BY COUNT(*) DESC LIMIT 1; 

select CONCAT('[[مستخدم:',user_name,'|',user_name,']]'), COUNT(*) from logging inner join user on user.user_id = logging.log_user where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_type in ("block", "protect", "delete", "rights") group by logging.log_user having COUNT(*)>1 ORDER BY COUNT(*) DESC LIMIT 1; 

select CONCAT('[[مستخدم:',user_name,'|',user_name,']]'), COUNT(*) from logging inner join user on user.user_id = logging.log_user where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_type in ("delete"") group by logging.log_user having COUNT(*)>1 ORDER BY COUNT(*) DESC LIMIT 1; 

select CONCAT('[[مستخدم:',user_name,'|',user_name,']]'), COUNT(*) from logging inner join user on user.user_id = logging.log_user where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_type in ("block") group by logging.log_user having COUNT(*)>1 ORDER BY COUNT(*) DESC LIMIT 1; 

select CONCAT('[[مستخدم:',user_name,'|',user_name,']]'), COUNT(*) from logging inner join user on user.user_id = logging.log_user where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_type in ("protect") group by logging.log_user having COUNT(*)>1 ORDER BY COUNT(*) DESC LIMIT 1; 

select CONCAT('[[مستخدم:',user_name,'|',user_name,']]'), COUNT(*) from logging inner join user on user.user_id = logging.log_user where log_timestamp BETWEEN 20190101000000 AND 20191231235959 and log_type in ("rights") group by logging.log_user having COUNT(*)>1 ORDER BY COUNT(*) DESC LIMIT 1;