USE arwiki_p;

    SET @end_date = '20230127';

    SET @start_date =DATE_SUB(@end_date, INTERVAL 6 month);

​

 SELECT @start_date,

   DATE_FORMAT(@start_date, "%W %e %M %Y") as 'بداية الفترة',

   DATE_FORMAT(@end_date, "%W %e %M %Y") as 'نهاية الفترة',

   DATEDIFF(@end_date,@start_date) as 'المدة'        ;

​

​

SELECT actor_name,

    SUM(IF( (log_type = 'delete'  AND log_action != 'restore'),1,0)) AS 'delete',

    SUM(IF( (log_type = 'delete'  AND log_action  = 'restore'),1,0)) AS 'restore',

    SUM(IF( (log_type = 'delete'  AND log_action = 'event'),1,0)) AS 'event',

    SUM(IF( (log_type = 'delete'  AND log_action = 'revision'),1,0)) AS 'revision',

    SUM(IF( (log_type = 'block'   AND log_action != 'unblock'),1,0)) AS 'block',

    SUM(IF( (log_type = 'block'   AND log_action  = 'unblock'),1,0)) AS 'unblock',

    SUM(IF( (log_type = 'block'   AND log_action  = 'reblock'),1,0)) AS 'reblock',

    SUM(IF( (log_type = 'protect' AND log_action != 'unprotect'),1,0)) AS 'protect',

    SUM(IF( (log_type = 'protect' AND log_action  = 'unprotect'),1,0)) AS 'unprotect',

    SUM(IF( (log_type = 'protect' AND log_action  = 'modify'),1,0)) AS 'modify',

    SUM(IF( log_type  = 'rights',1,0)) AS 'rights',

    SUM(IF( log_type != '',1,0)) AS 'total'

    FROM logging

    INNER JOIN actor ON logging.log_actor = actor.actor_id

    WHERE log_timestamp between @start_date and @end_date

    AND log_type IS NOT NULL

    AND log_action IS NOT NULL

    AND actor_name IN (SELECT user_name FROM user_groups INNER JOIN user ON user_id = ug_user WHERE ug_group = 'sysop')

    AND log_type IN ('block', 'delete', 'protect', 'rights')

    AND log_action NOT IN ('delete_redir', 'autopromote', 'move_prot')

    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 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')

    GROUP BY actor_name

    ORDER BY 'total' DESC;