This query is marked as a draft This query has been published by لوقا.

SQL

x
 
select  actor_name as "username", count(rev_actor) as "edit_count",
IF(actor_name in (
  select replace(pl_title,"_"," ") from pagelinks
  join page on page.page_id = pagelinks.pl_from 
  where page.page_namespace in (4) 
  and page_title like "مشروع_ويكي_العراق/المساهمون"
  and pl_namespace in (2)
  and pl_from_namespace in (4)
), "YES", "NO") as "in_WikiProject"
from revision 
JOIN actor ON actor_id = rev_actor
#where rev_timestamp > DATE_SUB(NOW(), INTERVAL 3 MONTH)
where rev_timestamp > DATE_SUB(NOW(), INTERVAL 2 DAY)
AND actor_name NOT IN (SELECT user_name FROM user_groups JOIN user ON user_id = ug_user WHERE ug_group = 'bot')
#todo: use join
and rev_page in (
  select page.page_id from page where page.page_namespace in (0)
    and page.page_title in (
         select p0.page_title from categorylinks
         inner join page p0 on p0.page_id = categorylinks.cl_from
         where categorylinks.cl_to like "مقالات_مشروع_ويكي_العراق" and categorylinks.cl_type = "page" and p0.page_namespace in (1) 
    ) 
)
GROUP BY rev_actor
HAVING count(rev_actor) > 10
ORDER BY COUNT(rev_actor) DESC;
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.

Checking query status...