WikiAward 2018: Tatar Wikipedia edits by users

SQL

use knwiki_p;
select
	t.rev_user_text,
    t.cnt
from (
    select
        r.rev_user,
        r.rev_user_text,
        count(*) cnt
    from revision r
    inner join page p
        on r.rev_page = p.page_id
    where
        p.page_namespace = 8
        and r.rev_timestamp >= "20240726000000"
        and r.rev_timestamp < "20240729000000"
    group by
        r.rev_user,
        r.rev_user_text
    order by cnt desc
    limit 1000
) t
left join user_groups ug
	on ug.ug_user = t.rev_user
    and ug.ug_group = 'bot'
where ug.ug_user is null
order by t.cnt desc
limit 10

WITH deleted AS (
  	WITH totalcount AS (
    SELECT MAX(ar_rev_id) AS REVUNIQ, DATE_FORMAT(ar_timestamp, '%Y-%m') AS MONTH
    FROM archive
    GROUP BY ar_title
  )
  SELECT COUNT(REVUNIQ) AS Del, MONTH
  FROM totalcount
  GROUP BY MONTH
  ORDER BY MONTH DESC)
  
  ,created AS (
  SELECT COUNT(page_title) AS cre, DATE_FORMAT(page_touched,'%Y-%m') AS creMONTH
  FROM page
  WHERE page.page_is_new=1
  GROUP BY DATE_FORMAT(page_touched,'%Y-%m') DESC)
  
SELECT (100.0*(deleted.Del/created.cre)), Del, cre, MONTH
FROM deleted
JOIN created ON deleted.MONTH=created.creMONTH
GROUP BY MONTH
ORDER BY MONTH DESC
SQL

Checking query status...
