use arwiki_p;
/*عدد التعديلات الكلي*/
select count(*) from revision where rev_timestamp like "2019%";
/*عدد التعديلات البوتية*/
select count(*) from revision
inner join actor
on actor_id = rev_actor
where rev_timestamp like "2019%"
and actor_user in (select ug_user from user_groups where ug_user = actor_user and ug_group = "bot");
/*عدد تعديلات المجهولين*/
select * from revision
inner join actor
on actor_id = rev_actor
where rev_timestamp like "2019%"
and actor_user not in (select user_id from user where user_id = actor_user);
/*عدد تعديلات المحررين*/
select * from revision
inner join actor
on actor_id = rev_actor
where rev_timestamp like "2019%"
and actor_user in (select ug_user from user_groups where ug_user = actor_user and ug_group = "editor");
/*عدد تعديلات المحررين بدون البوتات*/
select * from revision
inner join actor
on actor_id = rev_actor
where rev_timestamp like "2019%"
and actor_user in (select ug_user from user_groups where ug_user = actor_user and ug_group = "editor")
and actor_user not in (select ug_user from user_groups where ug_user = actor_user and ug_group = "bot");
/*عدد تعديلات المراجعين تلقائيا*/
select * from revision
inner join actor
on actor_id = rev_actor
where rev_timestamp like "2019%"
and actor_user in (select ug_user from user_groups where ug_user = actor_user and ug_group = "autoreview");
/*أكثر المستخدمين تواجدًا خلال العام*/
select actor_name as "اسم المستخدم", count(*) as "عدد الأيام" from(
select rev_user_text, count(*)
from revision
where rev_timestamp like "2019%"
and rev_user_text in (select user_name
from user
where user_name = rev_user_text
and user_id in (select ug_user from user_groups
where ug_user = user_id
and ug_group like "editor"))
group by rev_user_text, (DAY(rev_timestamp)), (MONTH(rev_timestamp))) as x
group by rev_user_text
order by count(*) desc
limit 5;
/*عدد الحسابات المنشأة*/
select count(*) from logging
where log_type like "newusers"
and log_timestamp like "2019%";
/*عدد المستخدمين الذين أجروا تعديلًا واحدأ على الأقل*/
select count(*) from user
inner join actor
on actor_user = user_id
where actor_id in (select rev_actor from revision where rev_actor = actor_id and rev_timestamp like "2019%");
/*عدد الصفحات الكلي*/
select count(*) from revision where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page not in (select page_id from page where page_is_redirect = 1 and page_id = rev_page);
/*عدد المقالات*/
select count(*) from revision where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 0 and page_is_redirect = 0);
/*عدد التصانيف*/
select count(*) from revision where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 14 and page_is_redirect = 0);
/*عدد القوالب*/
select count(*) from revision where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 10 and page_is_redirect = 0);
/*عدد المقالات البوتية*/
select count(*) from revision
inner join actor
on actor_id = rev_actor
where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 0 and page_is_redirect = 0)
and actor_user in (select ug_user from user_groups where ug_user = actor_user and ug_group = "bot");
/*عدد مقالات المجهولين*/
select count(*) from revision
inner join actor
on actor_id = rev_actor
where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 0 and page_is_redirect = 0)
and actor_user not in (select user_id from user where user_id = actor_user);
/*عدد مقالات المحررين*/
select count(*) from revision
inner join actor
on actor_id = rev_actor
where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 0 and page_is_redirect = 0)
and actor_user in (select ug_user from user_groups where ug_user = actor_user and ug_group = "editor");
/*عدد مقالات المراجعين تلقائيا*/
select count(*) from revision
inner join actor
on actor_id = rev_actor
where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 0 and page_is_redirect = 0)
and actor_user in (select ug_user from user_groups where ug_user = actor_user and ug_group = "autoreview");
/*عدد المقالات اليتيمة*/
select count(*) from revision where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 0 and page_is_redirect = 0)
and rev_page in (select cl_from from categorylinks where cl_from = rev_page and cl_title = "جميع_المقالات_اليتيمة");
/*عدد مقالات البذور*/
select count(*) from revision where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 0 and page_is_redirect = 0)
and rev_page in (select cl_from from categorylinks where cl_from = rev_page and cl_title = "جميع_مقالات_البذور");
/*عدد المقالات غير المصنفة*/
select count(*) from revision where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 0 and page_is_redirect = 0)
and rev_page in (select cl_from from categorylinks where cl_from = rev_page and cl_title = "جميع_المقالات_غير_المصنفة");
/*عدد المقالات التي بحاجة لبوابات*/
select count(*) from revision where rev_timestamp like"2019%" and rev_parent_id = 0
and rev_page in (select page_id from page where page_id = rev_page and page_namespace = 0 and page_is_redirect = 0)
and rev_page in (select cl_from from categorylinks where cl_from = rev_page and cl_title = "مقالات_بحاجة_لشريط_بوابات");
/*أكثر الإداريين نشاطا*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and (log_type = "protect" or log_type = "delete" or log_type = "block" or log_type = "rights")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر الإداريين حذفا*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and (log_type = "delete")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر الإداريين منعا*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and (log_type = "block")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر الإداريين تغييرا للصلاحيات*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and (log_type = "rights")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر الإداريين حماية*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and (log_type = "protect")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر المستخدمون إنشاء للمقالات*/
select actor_name , COUNT(*)
from revision
inner join actor
on actor.actor_id = revision.rev_actor
where rev_timestamp like "2019%"
and rev_parent_id = 0
and rev_page in (select page_id from page where page_is_redirect = 0 and page_id = rev_page and page_namespace = 0)
group by revision.rev_actor
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر المستخدمون رفعا للصور*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and log_type = "upload"
group by logging.log_actor
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر المستخدمون مراجعة للتعديلات*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and log_action = "approve"
group by logging.log_actor
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر المستخدمون مراجعة للصفحات الجديدة*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and log_action = "approve"
group by logging.log_actor
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر المستخدمون حسب عدد التعديلات*/
select actor_name , COUNT(*)
from revision
inner join actor
on actor.actor_id = revision.rev_actor
where rev_timestamp like "2019%"
group by revision.rev_actor
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر المستخدمون حسب عدد مرات الشكر*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and log_type = "thanks"
group by logging.log_actor
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر المستخدمون استرجاعا*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and log_type = "thanks"
group by logging.log_actor
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر المقالات تعديلا*/
select page_title, count(*)
from page
inner join revision
on page_id = rev_page
where page_namespace = 0
and page_is_redirect = 0
and rev_timestamp like "2019%"
group by page_title
order by count(*) desc
limit 5;
/*أقل المقالات تعديلا*/
select page_title, count(*)
from page
inner join revision
on page_id = rev_page
where page_namespace = 0
and rev_timestamp like "2019%"
and page_is_redirect = 0
group by page_title
order by count(*) asc
limit 5;
/*أكثر البوتات تعديلا*/
select actor_name , COUNT(*)
from revision
inner join actor
on actor.actor_id = revision.rev_actor
where rev_timestamp like "2019%"
and actor_user in (select ug_user from user_groups where ug_user = actor_user and ug_group = "bot")
group by revision.rev_actor
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أقل البوتات تعديلا*/
select actor_name , COUNT(*)
from revision
inner join actor
on actor.actor_id = revision.rev_actor
where rev_timestamp like "2019%"
and actor_user in (select ug_user from user_groups where ug_user = actor_user and ug_group = "bot")
group by revision.rev_actor
ORDER BY COUNT(*) asc
LIMIT 5;
/*عدد العمليات*/
select COUNT(*)
from logging
where log_timestamp like "2019%"
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*عدد عمليات الحماية*/
select COUNT(*)
from logging
where log_timestamp like "2019%"
and log_type = "protect"
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*عدد عمليات المنع*/
select COUNT(*)
from logging
where log_timestamp like "2019%"
and log_type = "block"
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*عدد عمليات النقل*/
select COUNT(*)
from logging
where log_timestamp like "2019%"
and log_type = "move"
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*عدد عمليات تعديل الصلاحيات*/
select COUNT(*)
from logging
where log_timestamp like "2019%"
and log_type = "rights"
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*عدد عمليات الشكر*/
select COUNT(*)
from logging
where log_timestamp like "2019%"
and log_type = "thanks"
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*عدد عمليات الحذف*/
select COUNT(*)
from logging
where log_timestamp like "2019%"
and log_type = "delete"
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*عدد ضربات مرشح الإساءة*/
select COUNT(*)
from abuse_filter_log
where afl_timestamp like "2019%";
/*أكثر المستخدمين ضربًا لمرشحات الإساءة*/
select afl_user_text, COUNT(*)
from abuse_filter_log
where afl_timestamp like "2019%"
group by afl_user_text
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر المرشحات عملًا*/
select afl_filter, COUNT(*)
from abuse_filter_log
where afl_timestamp like "2019%"
group by afl_filter
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر البيروقراط نشاطا*/
SELECT user_name,
( (SELECT Count(*)
FROM logging
INNER JOIN actor
ON actor_id = log_actor
WHERE actor_user = user_id
AND log_type = "rights"
AND log_timestamp LIKE "2019%"
AND log_params LIKE '%5::newgroups%"bot"%'
AND log_params NOT LIKE '%::oldgroups"%"sysop"%5::newgroups%'
AND log_params NOT LIKE '%::oldgroups"%"bot"%5::newgroups%')
+ (SELECT Count(*)
FROM logging
INNER JOIN actor
ON actor_id = log_actor
WHERE actor_user = user_id
AND log_type = "rights"
AND log_params LIKE '%5::newgroups%"sysop"%'
AND log_timestamp LIKE "2019%"
AND log_params NOT LIKE '%::oldgroups"%"sysop"%5::newgroups%'
)
+ (SELECT Count(*)
FROM logging
INNER JOIN actor
ON actor_id = log_actor
WHERE actor_user = user_id
AND log_type = "rights"
AND log_params LIKE '%5::newgroups%"bureaucrat"%'
AND log_timestamp LIKE "2019%"
AND log_params NOT LIKE
'%::oldgroups"%"bureaucrat"%5::newgroups%')
+ (SELECT Count(*)
FROM logging
INNER JOIN actor
ON actor_id = log_actor
WHERE actor_user = user_id
AND log_type = "rights"
AND log_params LIKE '%5::newgroups%"accountcreator"%'
AND log_timestamp LIKE "2019%"
AND log_params NOT LIKE
'%::oldgroups"%"accountcreator"%5::newgroups%'
)
+ (SELECT Count(*)
FROM logging
INNER JOIN actor
ON actor_id = log_actor
WHERE actor_user = user_id
AND log_type = "rights"
AND log_params LIKE '%5::newgroups%"import"%'
AND log_timestamp LIKE "2019%"
AND log_params NOT LIKE
'%::oldgroups"%"import"%5::newgroups%')
+ (SELECT Count(*)
FROM logging
INNER JOIN actor
ON actor_id = log_actor
WHERE actor_user = user_id
AND log_type = "rights"
AND log_timestamp LIKE "2019%"
AND log_params LIKE '%::oldgroups"%"import"%5::newgroups%'
AND log_params NOT LIKE '%5::newgroups%"import"%')
+ (SELECT Count(*)
FROM logging
INNER JOIN actor
ON actor_id = log_actor
WHERE actor_user = user_id
AND log_type = "rights"
AND log_timestamp LIKE "2019%"
AND log_params LIKE
'%::oldgroups"%"accountcreator"%5::newgroups%'
AND log_params NOT LIKE '%5::newgroups%"accountcreator"%')
+ (SELECT Count(*)
FROM logging
INNER JOIN actor
ON actor_id = log_actor
WHERE actor_user = user_id
AND log_type = "rights"
AND log_timestamp LIKE "2019%"
AND log_params LIKE '%::oldgroups"%"bot"%5::newgroups%'
AND log_params NOT LIKE '%::oldgroups"%"sysop"%5::newgroups%'
AND log_params NOT LIKE '%5::newgroups%"bot"%')
+ (SELECT Count(*)
FROM revision
INNER JOIN actor
ON actor_id = rev_actor
WHERE actor_user = user_id
AND rev_page = 213729
AND rev_minor_edit = 0
AND rev_timestamp LIKE "2019%") ) AS "المجموع"
FROM user
INNER JOIN user_groups
ON ug_user = user_id
WHERE ug_group = "bureaucrat";
/*عدد عمليات تغيير الاسماء*/
select COUNT(*)
from logging
where log_timestamp like "2019%"
and log_type = "renameuser"
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر مغيري الأسماء*/
select actor_name , COUNT(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and (log_type = "renameuser")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أكثر المشكورين*/
select log_title, count(*)
from logging
inner join actor
on actor.actor_id = logging.log_actor
where log_timestamp like "2019%"
and (log_type = "thanks")
group by logging.log_title
ORDER BY COUNT(*) DESC
LIMIT 5;
/*أول تعديل*/
select rev_id from revision where rev_timestamp like "2019%"
order by rev_timestamp asc
limit 1;
/*آخر تعديل*/
select rev_id from revision where rev_timestamp like "2019%"
order by rev_timestamp desc
limit 1;
/*أول مقالة*/
select * from revision where rev_timestamp like "2019%"
and rev_parent_id = 0
and rev_page in (select page_id from page where page_namespace = 0 and page_is_redirect = 0 and page_id = rev_page)
order by rev_timestamp asc
limit 1;
/*آخر مقالة*/
select * from revision where rev_timestamp like "2019%"
and rev_parent_id = 0
and rev_page in (select page_id from page where page_namespace = 0 and page_is_redirect = 0 and page_id = rev_page)
order by rev_timestamp desc
limit 1;
