بوت: توزيع أوسمة

SQL

/*
# يقوم بحساب التعديلات الغير محذوفه فقط 
SELECT actor_name,
       (SELECT COUNT(*) FROM revision WHERE rev_actor = actor_id AND rev_timestamp < 20221201000000) AS revisions_until_yesterday,
       (SELECT COUNT(*) FROM revision WHERE rev_actor = actor_id AND rev_timestamp <= 20230131235959) AS total_edits
FROM actor
WHERE (SELECT COUNT(*) FROM revision WHERE rev_actor = actor_id AND rev_timestamp < 20221201000000) < 500
AND (SELECT COUNT(*) FROM revision WHERE rev_actor = actor_id AND rev_timestamp <= 20230131235959) >= 500
and actor_name not in (select REPLACE(pl_title, '_', ' ') from pagelinks where  pl_from = 7519882);

# يقوم بحساب التعديلات المحذوفه والظاهرة 
SELECT actor_name, sum_yc, sum_tc
FROM (
  SELECT actor.actor_name,
         SUM(yc_rev.cnt) + SUM(yc_arc.cnt) AS sum_yc,
         SUM(tc_rev.cnt) + SUM(tc_arc.cnt) AS sum_tc
  FROM actor
  JOIN (SELECT rev_actor, COUNT(rev_id) AS cnt FROM revision WHERE rev_timestamp < 20221201000000 GROUP BY rev_actor) yc_rev
  ON yc_rev.rev_actor = actor.actor_id
  JOIN (SELECT ar_actor, COUNT(ar_id) AS cnt FROM archive WHERE ar_timestamp < 20221201000000 GROUP BY ar_actor) yc_arc
  ON yc_arc.ar_actor = actor.actor_id
  JOIN (SELECT rev_actor, COUNT(rev_id) AS cnt FROM revision WHERE rev_timestamp <= 20230131235959 GROUP BY rev_actor) tc_rev
  ON tc_rev.rev_actor = actor.actor_id
  JOIN (SELECT ar_actor, COUNT(ar_id) AS cnt FROM archive WHERE ar_timestamp <= 20230131235959 GROUP BY ar_actor) tc_arc
  ON tc_arc.ar_actor = actor.actor_id
  WHERE actor_name not IN (SELECT REPLACE(pl_title, '_', ' ') FROM pagelinks WHERE  pl_from = 7519882)
  GROUP BY actor.actor_name
) sub
WHERE sum_yc < 500 AND sum_tc >= 500

*/


SELECT actor_name,
       (SELECT COUNT(*) FROM revision WHERE rev_actor = actor_id AND rev_timestamp < 20221201000000) AS sum_yc,
       (SELECT COUNT(*) FROM revision WHERE rev_actor = actor_id AND rev_timestamp <= 20230604235959) AS sum_tc
FROM actor
WHERE (SELECT COUNT(*) FROM revision WHERE rev_actor = actor_id AND rev_timestamp < 20221201000000) < 30000
AND (SELECT COUNT(*) FROM revision WHERE rev_actor = actor_id AND rev_timestamp <= 20230604235959) >= 30000
and actor_name not in (select REPLACE(pl_title, '_', ' ') from pagelinks where  pl_from = 7519882)
# to hoide test edits
and actor_name not in (select REPLACE(pl_title, '_', ' ') from pagelinks where  pl_from = 9224685);
SQL

Checking query status...
