set @list = "המקבץ השבועי/מזרח/דף משנה";

​

use hewiki_p;

select rev_user_text, pl_title from pagelinks join page

on page_title = pl_title

join revision

on rev_page = page_id

where pl_from in

(select page_id from page

 where page_title = replace(@list, " ", "_")

 and page_namespace = 4)

and page_namespace = 0

and rev_user > 0

and rev_parent_id = 0

and (substring(rev_timestamp, 1, 4) = "2016" and

     ((substring(rev_timestamp, 5, 5) = "04302" and

       substring(rev_timestamp, 10, 1) in ("1", "2", "3")) or

      (substring(rev_timestamp, 5, 3) = "050" and

       substring(rev_timestamp, 8, 1) in ("1", "2", "3", "4", "5", "6")) or

      (substring(rev_timestamp, 5, 4) = "0507" and

       (substring(rev_timestamp, 9, 1) in ("0", "1") or

        substring(rev_timestamp, 9, 2) = "20"))))

order by rev_user_text, pl_title;

select rev_user_text, pl_title from pagelinks join page

on page_title = pl_title

join revision

on rev_page = page_id

where pl_from in

(select page_id from page

 where page_title = replace(@list, " ", "_")

 and page_namespace = 4)

and page_namespace = 0

and rev_user > 0

and not exists

(select * from user_groups

 where rev_user = ug_user

 and ug_group = "bot")

and (substring(rev_timestamp, 1, 4) = "2016" and

     ((substring(rev_timestamp, 5, 5) = "04302" and

       substring(rev_timestamp, 10, 1) in ("1", "2", "3")) or

      (substring(rev_timestamp, 5, 3) = "050" and

       substring(rev_timestamp, 8, 1) in ("1", "2", "3", "4", "5", "6")) or

      (substring(rev_timestamp, 5, 4) = "0507" and

       (substring(rev_timestamp, 9, 1) in ("0", "1") or

        substring(rev_timestamp, 9, 2) = "20"))))

and not exists

(select * from revision

where rev_page = page_id

and rev_user > 0

and rev_parent_id = 0

and (substring(rev_timestamp, 1, 4) = "2016" and

     ((substring(rev_timestamp, 5, 5) = "04302" and

       substring(rev_timestamp, 10, 1) in ("1", "2", "3")) or

      (substring(rev_timestamp, 5, 3) = "050" and

       substring(rev_timestamp, 8, 1) in ("1", "2", "3", "4", "5", "6")) or

      (substring(rev_timestamp, 5, 4) = "0507" and

       (substring(rev_timestamp, 9, 1) in ("0", "1") or

        substring(rev_timestamp, 9, 2) = "20")))));

#group by rev_user_text

#order by count(pl_title) desc, rev_user_text

select * from user_groups join user on ug_user = user_id where ug_group = "bot"

​