احصاءات

This query is marked as a draft This query has been published by البربوشي.
SQL

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.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
