إحصاءات العام

This query is marked as a draft This query has been published by ASammour.
SQL

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;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
