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 20170101000000;

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 20170101000000;

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 20180101000000 
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 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
group by logging.log_user, log_action
having COUNT(*)>1
ORDER BY COUNT(*) DESC
limit 30;
SQL

Checking query status...
