SQL
x
use arwiki_p;
SET @start_date = '20221201000000';
SET @end_date = '20230101000000';
select count(*) as "إنشاء الصفحات"
from revision
where rev_timestamp between @start_date and NOW()
and rev_parent_id = 0;
select count(*) as "المجهولون"
from revision
where rev_timestamp between @start_date and NOW()
and rev_user= 0;
select count(*) as "المحررون"
from revision
where rev_timestamp between @start_date and NOW()
and rev_user in (select ug_user from user_groups where ug_user = rev_user and ug_group = "editor");
select count(*) as "المراجعون تلقائيا"
from revision
where rev_timestamp between @start_date and NOW()
and rev_user in (select ug_user from user_groups where ug_user = rev_user and ug_group = "autoreview");
select count(*) as "البوتات"
from revision
where rev_timestamp between @start_date and NOW()
and rev_user in (select ug_user from user_groups where ug_user = rev_user and ug_group = "bot")
and rev_user not in (select ug_user from user_groups where ug_user = rev_user and ug_group = "editor");
select count(*) as "غير ذلك"
from revision
where rev_timestamp between @start_date and NOW()
and rev_user not in (select ug_user from user_groups where ug_user = rev_user and ug_group = "bot")
and rev_user not in (select ug_user from user_groups where ug_user = rev_user and ug_group = "autoreview")
and rev_user not in (select ug_user from user_groups where ug_user = rev_user and ug_group = "editor");
select count(*) as "عدد المستخدمين المسجلين"
from user
where user_registration between @start_date and NOW();
select count(*) as "عدد المستخدمين المسجلين الذين قاموا بتعديل واحد على الأقل"
from user
where user_registration between @start_date and NOW()
and user_editcount> 1;
select Count(Distinct rev_user) as "عدد المستخدمين الذين قاموا بالتعديل"
from revision
where rev_timestamp between @start_date and NOW();
select user_name as "قائمة المستخدمين الجدد"
from user
where user_registration between @start_date and NOW();
select count(*) as "عدد المقالات الجديدة"
from page
inner join revision
on page_id = rev_page
where page_namespace = 0
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
and rev_parent_id = 0;
select count(*) as "عدد القوالب الجديدة"
from page
inner join revision
on page_id = rev_page
where page_namespace = 10
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
and rev_parent_id = 0;
select count(*) as "عدد التصنيفات الجديدة"
from page
inner join revision
on page_id = rev_page
where page_namespace = 14
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
and rev_parent_id = 0;
select count(*) as "عدد الصور الجديدة"
from logging
where log_timestamp between @start_date and NOW()
and log_type = "upload";*/
select page_title as "أول مقالة"
from page
inner join revision
on rev_page = page_id
where page_namespace = 0
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
order by rev_timestamp desc limit 1;
select page_title as "آخر مقالة"
from page
inner join revision
on rev_page = page_id
where page_namespace = 0
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
order by rev_timestamp asc limit 1;
select count(*) as "عدد المقالات الجديدة غير المصنفة"
from page
inner join revision
on page_id = rev_page
where page_namespace = 0
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
and rev_parent_id = 0
and page_id in (select cl_from from categorylinks where cl_from=page_id and cl_to = "جميع_المقالات_غير_المصنفة");
select count(*) as "عدد المقالات الجديدة اليتيمة"
from page
inner join revision
on page_id = rev_page
where page_namespace = 0
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
and rev_parent_id = 0
and page_id in (select cl_from from categorylinks where cl_from=page_id and cl_to = "جميع_المقالات_اليتيمة");
select count(*) as "عدد المقالات الجديدة البذرة"
from page
inner join revision
on page_id = rev_page
where page_namespace = 0
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
and rev_parent_id = 0
and page_id in (select cl_from from categorylinks where cl_from=page_id and cl_to = "جميع_مقالات_البذور");
select count(*) as "عدد المقالات الجديدة بدون مصدر"
from page
inner join revision
on page_id = rev_page
where page_namespace = 0
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
and rev_parent_id = 0
and page_id in (select cl_from from categorylinks where cl_from=page_id and cl_to = "جميع_المقالات_بدون_مصدر");
select count(*) as "عدد المقالات الجديدة ذات النهاية المسدودة"
from page
inner join revision
on page_id = rev_page
where page_namespace = 0
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
and rev_parent_id = 0
and page_id in (select cl_from from categorylinks where cl_from=page_id and cl_to = "جميع_مقالات_النهاية_المسدودة");
select count(*) as "عدد المقالات الجديدة التي بحاجة لشريط بوابات"
from page
inner join revision
on page_id = rev_page
where page_namespace = 0
and page_is_redirect = 0
and rev_timestamp between @start_date and NOW()
and rev_parent_id = 0
and page_id in (select cl_from from categorylinks where cl_from=page_id and cl_to = "مقالات_بحاجة_لشريط_بوابات");
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.