Untitled query #35015

SQL

SET @activation_period = 5; /* 5 days */
SET @n = 1; /* One activation edit */
SET @trial_period = 30; /* 30 days */
SET @survival_period = 30; /* 30 days*/
SET @m = 1; /* One survival edit */
SET @start_date = "20190101";
SET @end_date = "20190201";

SELECT
    user_id,
    user_name,
    user_registration,
    (
        UNIX_TIMESTAMP(NOW()) <
        UNIX_TIMESTAMP(DATE_ADD(user_registration, INTERVAL @trial_period+@survival_period DAY))
    ) AS censored
FROM (
    SELECT
        user_id,
        user_name,
        user_registration,
        SUM(
            rev_timestamp BETWEEN
                user_registration AND
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @activation_period DAY), "%Y%m%d%H%i%M")
        ) AS activation_edits,
        SUM(
            rev_timestamp BETWEEN
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @trial_period DAY), "%Y%m%d%H%i%M") AND
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @trial_period+@survival_period DAY), "%Y%m%d%H%i%M")
        ) AS surviving_edits
    FROM user
    LEFT JOIN revision_userindex ON
        user_id = rev_user AND
        (
            rev_timestamp BETWEEN
                user_registration AND
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @activation_period DAY), "%Y%m%d%H%i%M") OR 
            rev_timestamp BETWEEN
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @trial_period DAY), "%Y%m%d%H%i%M") AND
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @trial_period+@survival_period DAY), "%Y%m%d%H%i%M")
        )
    WHERE user_registration BETWEEN @start_date AND @end_date
  	GROUP BY 1,2,3
    UNION ALL
    SELECT
        user_id,
        user_name,
        user_registration,
        SUM(
            ar_timestamp BETWEEN
                user_registration AND
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @activation_period DAY), "%Y%m%d%H%i%M")
        ) AS activation_edits,
        SUM(
            ar_timestamp BETWEEN
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @trial_period DAY), "%Y%m%d%H%i%M") AND
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @trial_period+@survival_period DAY), "%Y%m%d%H%i%M")
        ) AS surviving_edits
    FROM user
    LEFT JOIN archive_userindex ON
        user_id = ar_user AND
        (
            ar_timestamp BETWEEN
                user_registration AND
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @activation_period DAY), "%Y%m%d%H%i%M") OR 
            ar_timestamp BETWEEN
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @trial_period DAY), "%Y%m%d%H%i%M") AND
                DATE_FORMAT(DATE_ADD(user_registration, INTERVAL @trial_period+@survival_period DAY), "%Y%m%d%H%i%M")
        )
    WHERE user_registration BETWEEN @start_date AND @end_date
  GROUP BY 1,2,3
) split_edit_counts
GROUP BY user_id, user_name, user_registration, censored
HAVING SUM(activation_edits) > @n AND SUM(surviving_edits) > @m;
SQL

Checking query status...
