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;