General stats arwiki 2017

SQL

use arwiki_p;


select log_action, COUNT(*) 
from logging 
where log_timestamp BETWEEN 20170101000000 AND 20171231235959 
group by log_action;


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

SELECT COUNT(user_name) as "registred"
FROM user 
WHERE user_registration BETWEEN 20170101000000 AND 20171231235959;


SELECT  MONTH(rev_timestamp), 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 20170101000000 and 20171231235959 and r.rev_parent_id=0
GROUP BY YEAR(rev_timestamp), MONTH(rev_timestamp)
ORDER BY COUNT(*) DESC;

select count(*) AS "edits" from revision where rev_timestamp between 20170101000000 AND 20171231235959;

select count(*) as "IP" 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 20170101000000 AND 20171231235959;

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 20170101000000 AND 20171231235959 
GROUP BY rev_user_text
ORDER BY COUNT(rev_user_text) DESC
LIMIT 30) s;

select CONCAT('[[مستخدم:',user_name,'|',user_name,']]'), COUNT(*), log_action
from logging
inner join user
on user.user_id = logging.log_user
where log_timestamp BETWEEN 20170101000000 AND 20171231235959
and log_action in ("block","approve-i","approve","protect","thank","rights","delete")
group by logging.log_user, log_action
having COUNT(*)>1
ORDER BY COUNT(*) DESC
limit 300;

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 20170101000000 AND 20171231235959
AND parent.rev_timestamp BETWEEN 20170101000000 AND 20171231235959
GROUP BY user_name
having byte_count > 0
ORDER BY byte_count DESC
LIMIT 10;
SQL

Checking query status...
