This query is marked as a draft This query has been published by لوقا.

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.

Checking query status...