مستخدم:لوقا/ملعب

select count(*) from revision where rev_timestamp between 20240101000000 AND 20241231235959;

SELECT  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 20240101000000 and 20241231235959 and r.rev_parent_id=0
ORDER BY COUNT(*) DESC
  LIMIT 1;

SELECT  COUNT(*) AS "عدد القوالب"
FROM revision r
join page p on p.page_id=r.rev_page
WHERE p.page_namespace=10 and p.page_is_redirect=0 and r.rev_timestamp between 20240101000000 and 20241231235959 and r.rev_parent_id=0
ORDER BY COUNT(*) DESC
  LIMIT 1;

SELECT  COUNT(*) AS "عدد التصانيف"
FROM revision r
join page p on p.page_id=r.rev_page
WHERE p.page_namespace=14 and p.page_is_redirect=0 and r.rev_timestamp between 20240101000000 and 20241231235959 and r.rev_parent_id=0
ORDER BY COUNT(*) DESC
  LIMIT 1;

 select COUNT(*) from logging where log_timestamp BETWEEN 20240101000000 AND 20241231235959 and log_action = "upload"

 select COUNT(*) from logging where log_timestamp BETWEEN 20240101000000 AND 20241231235959 and log_action = "move"

 select COUNT(*) from logging where log_timestamp BETWEEN 20240101000000 AND 20241231235959 and log_action = "delete"

 select COUNT(*) from logging where log_timestamp BETWEEN 20240101000000 AND 20241231235959 and log_action = "protect"

SELECT COUNT(new_posts.page_id) AS "عدد المقالات",
    DATE_FORMAT(new_posts.first_revision, '%Y-%m') AS month
FROM (
    SELECT p.page_id, MIN(r.rev_timestamp) AS first_revision
    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_parent_id = 0
    GROUP BY p.page_id
    HAVING first_revision BETWEEN 20230101000000 AND 20231231235959
) AS new_posts
GROUP BY month
ORDER BY month;

select CONCAT('[[مستخدم:',actor_name,'|',actor_name,']]'), COUNT(*)
from logging
INNER JOIN actor on logging.log_actor = actor_id
where log_timestamp BETWEEN 20240101000000 AND 20241231235959
and log_type in ("block")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 30;

select CONCAT('[[مستخدم:',actor_name,'|',actor_name,']]'), COUNT(*)
from logging
INNER JOIN actor on logging.log_actor = actor_id
where log_timestamp BETWEEN 20240101000000 AND 20241231235959
and log_type in ("delete")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 30;

select CONCAT('[[مستخدم:',actor_name,'|',actor_name,']]'), COUNT(*)
from logging
INNER JOIN actor on logging.log_actor = actor_id
where log_timestamp BETWEEN 20240101000000 AND 20241231235959
and log_type in ("protect")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 30;

select CONCAT('[[مستخدم:',actor_name,'|',actor_name,']]'), COUNT(*)
from logging
INNER JOIN actor on logging.log_actor = actor_id
where log_timestamp BETWEEN 20240101000000 AND 20241231235959
and log_type in ("rights")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 30;

select CONCAT('[[مستخدم:',actor_name,'|',actor_name,']]'), COUNT(*)
from logging
INNER JOIN actor on logging.log_actor = actor_id
where log_timestamp BETWEEN 20240101000000 AND 20241231235959
and log_type in ("block", "protect", "delete", "rights")
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 30;

select CONCAT('[[مستخدم:',actor_name,'|',actor_name,']]'), COUNT(*)
from logging
INNER JOIN actor on logging.log_actor = actor_id
where log_timestamp BETWEEN 20240101000000 AND 20241231235959
and log_action = "approve-i"
and log_namespace = 0
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 30;

select CONCAT('[[مستخدم:',actor_name,'|',actor_name,']]'), COUNT(*)
from logging
INNER JOIN actor on logging.log_actor = actor_id
where log_timestamp BETWEEN 20240101000000 AND 20241231235959
and log_action = "approve"
and log_namespace = 0
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 30;

select CONCAT('[[مستخدم:',actor_name,'|',actor_name,']]'), COUNT(*)
from logging
INNER JOIN actor on logging.log_actor = actor_id
where log_timestamp BETWEEN 20240101000000 AND 20241231235959
and log_type = "thanks"
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 30;

select CONCAT('[[مستخدم:',actor_name,'|',actor_name,']]'), COUNT(*)
from logging
INNER JOIN actor on logging.log_actor = actor_id
where log_timestamp BETWEEN 20240101000000 AND 20241231235959
and log_action IN ("move")
and log_namespace = 0
group by logging.log_actor
having COUNT(*)>1
ORDER BY COUNT(*) DESC
LIMIT 30;

SELECT CONCAT("[[مستخدم:",actor_name,"|",actor_name,"]]") AS user_name, SUM(CAST(rev.rev_len as signed)-CAST(parent.rev_len as signed)) AS byte_count, COUNT(rev.rev_id) as edit_count
FROM revision rev
INNER JOIN actor on rev.rev_actor = actor_id
JOIN revision parent
ON rev.rev_parent_id = parent.rev_id
INNER JOIN comment_revision on rev.rev_comment_id = comment_id
JOIN page
ON page_id = parent.rev_page
WHERE page_namespace = 0
and comment_text not like "%رجوع%"
and comment_text not like "%استرجاع%"
AND rev.rev_timestamp BETWEEN 20240101000000 AND 20241231235959
AND parent.rev_timestamp BETWEEN 20240101000000 AND 20241231235959
  and ucase(actor_name) not like ucase("%BOT") COLLATE utf8_general_ci
  and actor_name not like "%بوت%" collate utf8_general_ci
  and actor_name Not IN (SELECT user_name
                         FROM user_groups
                                  INNER JOIN user ON user_id = ug_user
                         WHERE ug_group = "bot")
  and actor_id NOT IN ("2579643")
  and actor_name not in ('New user message')
GROUP BY actor_name
having byte_count > 0
ORDER BY byte_count DESC
LIMIT 50;

SELECT  a.actor_name AS first_user, COUNT(new_posts.page_title) AS post_count_by_user
FROM (
    SELECT p.page_id, p.page_title, MIN(r.rev_timestamp) AS first_revision
    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_parent_id = 0
    GROUP BY p.page_id
    HAVING first_revision BETWEEN 20230101000000 AND 20231231235959
) AS new_posts
JOIN revision r ON new_posts.page_id = r.rev_page AND new_posts.first_revision = r.rev_timestamp
JOIN actor a ON r.rev_actor = a.actor_id
GROUP BY first_user
ORDER BY post_count_by_user DESC
LIMIT 20;

USE arwiki_p;
SELECT CONCAT("[[مستخدم:",actor_name,"|",actor_name,"]]") AS "المستخدم", COUNT(actor_name) AS "عدد التعديلات"
FROM revision
INNER JOIN actor on rev_actor = actor_id
WHERE rev_timestamp BETWEEN 20240101000000 AND 20241231235959
and (
 ucase(actor_name) like ucase("%BOT") COLLATE utf8_general_ci
  or actor_name like "%بوت%" collate utf8_general_ci
  or actor_name  IN (SELECT user_name
                         FROM user_groups
                                  INNER JOIN user ON user_id = ug_user
                         WHERE ug_group = "bot")
 
)
and actor_id NOT IN ("2579643")
GROUP BY actor_name
ORDER BY COUNT(actor_name) DESC
LIMIT 50

USE arwiki_p;
SELECT CONCAT("[[مستخدم:",actor_name,"|",actor_name,"]]") AS "المستخدم", COUNT(actor_name) AS "عدد التعديلات"
FROM revision
INNER JOIN actor on rev_actor = actor_id
WHERE rev_timestamp BETWEEN 20240101000000 AND 20241231235959
and (
 ucase(actor_name) like ucase("%BOT") COLLATE utf8_general_ci
  or actor_name like "%بوت%" collate utf8_general_ci
  or actor_name  IN (SELECT user_name
                         FROM user_groups
                                  INNER JOIN user ON user_id = ug_user
                         WHERE ug_group = "bot")
 
)
and actor_id NOT IN ("2579643")
GROUP BY actor_name
ORDER BY COUNT(actor_name) DESC
LIMIT 50

SELECT CONCAT("[[مستخدم:",actor_name,"|",actor_name,"]]") AS "المستخدم", COUNT(actor_name) AS "عدد التعديلات"
FROM revision
INNER JOIN actor on rev_actor = actor_id
WHERE rev_timestamp BETWEEN 20240101000000 AND 20241231235959
  and ucase(actor_name) not like ucase("%BOT") COLLATE utf8_general_ci
  and actor_name not like "%بوت%" collate utf8_general_ci
  and actor_name Not IN (SELECT user_name
                         FROM user_groups
                                  INNER JOIN user ON user_id = ug_user
                         WHERE ug_group = "bot")
  and actor_id NOT IN ("2579643")
  and actor_name not in ('New user message')
GROUP BY actor_name
ORDER BY COUNT(actor_name) DESC
LIMIT 50

SELECT COUNT(user_name)
FROM user 
WHERE user_registration BETWEEN 20240101000000 AND 20241231235959;

select count(rev_id) from 
revision 
join actor on rev_actor = actor_id
where actor_user is null and  rev_timestamp between 20240101000000 AND 20241231235959;

 select COUNT(*) from logging where log_timestamp BETWEEN 20240101000000 AND 20241231235959 and log_action = "block"

 select COUNT(*) from logging where log_timestamp BETWEEN 20240101000000 AND 20241231235959 and log_action = "rights"

 select COUNT(*) from logging where log_timestamp BETWEEN 20240101000000 AND 20241231235959 and log_type = "thanks"

SELECT
  page_title,
  COUNT(*)
FROM revision
JOIN page
ON page_id = rev_page
WHERE rev_timestamp BETWEEN 20240101000000 AND 20241231235959
and page_namespace = 0
GROUP BY page_namespace, page_title
ORDER BY COUNT(*) DESC, page_title ASC
LIMIT 50;