Untitled query #24167

SQL

use knwiki_p;
SET @n = 5; /* edits threshold */
SET @u = 30; /* activity unit in days */
SET @T = "20180301"; /* 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;
SQL

Checking query status...
