General stats arwiki 2006

SQL

use arwiki_p;


select log_action, COUNT(*) 
from logging 
where log_timestamp BETWEEN 20060101000000 AND 20061231235959 
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 20060101000000 and 20061231235959 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 20060101000000 AND 20061231235959;


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 20060101000000 and 20061231235959 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 20060101000000 AND 20061231235959;

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 20060101000000 AND 20061231235959;

SELECT  concat("{{ويكيبيديا:إحصاءات العام/بطاقة جدول/عنصر|1|", rev_user_text , "|", COUNT(rev_user_text), "}}")
FROM revision 
WHERE rev_timestamp BETWEEN 20060101000000 AND 20061231235959 
GROUP BY rev_user_text
ORDER BY COUNT(rev_user_text) DESC
LIMIT 30;

select concat("{{ويكيبيديا:إحصاءات العام/بطاقة جدول/عنصر|1|", user_name , "|",COUNT(*), "}}"), log_action
from logging
inner join user
on user.user_id = logging.log_user
where log_timestamp BETWEEN 20060101000000 AND 20061231235959
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
concat("{{ويكيبيديا:إحصاءات العام/بطاقة جدول/عنصر|1|", user_name, "|", SUM(CAST(rev.rev_len as signed)-CAST(parent.rev_len as signed)), "}}"), 
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 20060101000000 AND 20061231235959
AND parent.rev_timestamp BETWEEN 20060101000000 AND 20061231235959
GROUP BY user_name
having byte_count > 0
ORDER BY byte_count DESC
LIMIT 10;
SQL

Checking query status...
