SQL
x
use orwiki_p;
SET @n = 5; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20171001"; /* to replace with dynamic value */
SELECT
user
FROM (
/* Get revisions to content pages that are still visible */
SELECT
rev_user_text as user,
SUM(rev_id IS NOT NULL) AS revisions
FROM
revision
WHERE
rev_user != 0
AND
rev_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
GROUP BY user
UNION ALL
/* Get revisions to content pages that have been archived */
SELECT
ar_user as user,
SUM(ar_id IS NOT NULL) AS revisions
FROM
archive
WHERE
ar_user != 0
AND
ar_timestamp BETWEEN DATE_FORMAT(DATE_SUB(@T, INTERVAL @u DAY), "%Y%m%d%H%i%S") AND @T
GROUP BY
user
) AS user_content_revision_count
GROUP BY
user
HAVING
SUM(revisions) >= @n and SUM(revisions) < 101;
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.