SQL
x
WITH user_edits AS (
SELECT
a.actor_user AS user_id,
COUNT(*) AS total_edits,
SUM(CASE WHEN r.rev_timestamp BETWEEN '20220906000000' AND '20240701000000' THEN 1 ELSE 0 END) AS recent_edits
FROM
revision r
JOIN
actor a ON r.rev_actor = a.actor_id
WHERE
r.rev_timestamp < '20240701000000'
# skip bots
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 IN (SELECT user_name
FROM user_groups
INNER JOIN user ON user_id = ug_user
WHERE ug_group = "bot")
and actor_id NOT IN ("2579643")
and actor_name not in ('New user message')
# end skip bots
GROUP BY
a.actor_user
)
SELECT
user_id
FROM
user_edits
WHERE
total_edits > 300
AND recent_edits >= 20
limit 100;
# desc actor;
# 3532174
# select * from actor where actor_name like "لوقا";
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.