Untitled query #70216

SQL

SET @zaman = 365;
SELECT
  actor.actor_name AS Kullanici,
  GROUP_CONCAT(DISTINCT user_groups.ug_group) AS KullaniciGrubu, 
  page.page_title AS Sayfa, 
  COUNT(DISTINCT rev_id) AS DegisiklikSayisi
FROM revision JOIN page_restrictions ON (page_restrictions.pr_page = revision.rev_page AND (page_restrictions.pr_type = "edit" AND page_restrictions.pr_level = "sysop"))
  LEFT JOIN actor ON (revision.rev_actor = actor.actor_id)
  LEFT JOIN user_groups ON (actor.actor_user = user_groups.ug_user)
  LEFT JOIN page ON (revision.rev_page = page.page_id)  
  LEFT JOIN logging ON (revision.rev_page = logging.log_page)  
-- limit to the time --> @zaman
WHERE DATEDIFF(CURDATE(), rev_timestamp) < @zaman
-- limit the pages (Mainpage, MediaWiki:Spam-blacklist, AutoWikiBrowser permission pages)
-- AND ((page.page_title LIKE ('Anasayfa')) OR (page.page_title LIKE ('%Spam-blacklist%')) OR (page.page_title LIKE ('%AutoWikiBrowser/CheckPage%')))
-- or limit the pages with expiry time
-- AND page_restrictions.pr_expiry ='infinity'
-- protect action isn't in time --> @zaman 
-- (but if a page protected more than one time & even if only one of the actions outside of @zaman, this'll result false positive.
AND (logging.log_type = 'protect' AND NOT (DATEDIFF(CURDATE(), logging.log_timestamp) < @zaman)) 
AND NOT (
  (HAVING COUNT (
    SELECT DISTINCT logging.log_page 
    WHERE (logging.log_type = 'protect' AND (DATEDIFF(CURDATE(), logging.log_timestamp) > @zaman))) AS Sayii))
-- Group by article & user
GROUP BY Sayfa, Kullanici
-- order by rev count
ORDER BY DegisiklikSayisi DESC
LIMIT 100;
SQL

Checking query status...
