Untitled query #18526

SQL

USE enwiki_p;
SELECT 
	LEFT(user_registration, 6) AS month,
    SUM(activated) AS new_editors,
    SUM(surviving) AS surviving_editors,
    SUM(censored AND NOT surviving) AS editors_who_might_still_survive
FROM (
    SELECT
        user_id,
        user_name,
        user_registration,
        SUM(activation_edits) > 1 AS activated,
        SUM(activation_edits) > 1 AND SUM(surviving_edits) > 1 AS surviving,
        (
            UNIX_TIMESTAMP(NOW()) <
            UNIX_TIMESTAMP(DATE_ADD(user_registration, INTERVAL 60 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 30 DAY), "%Y%m%d%H%i%M")
            ) AS activation_edits,
            SUM(
                rev_timestamp BETWEEN
                    DATE_FORMAT(DATE_ADD(user_registration, INTERVAL 30 DAY), "%Y%m%d%H%i%M") AND
                    DATE_FORMAT(DATE_ADD(user_registration, INTERVAL 60 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 60 DAY), "%Y%m%d%H%i%M")
        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 30 DAY), "%Y%m%d%H%i%M")
            ) AS activation_edits,
            SUM(
                ar_timestamp BETWEEN
                    DATE_FORMAT(DATE_ADD(user_registration, INTERVAL 30 DAY), "%Y%m%d%H%i%M") AND
                    DATE_FORMAT(DATE_ADD(user_registration, INTERVAL 60 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 60 DAY), "%Y%m%d%H%i%M")
        GROUP BY 1,2,3)
    ) split_edit_counts
    GROUP BY user_id, user_name, user_registration
  ) AS user_survival
GROUP BY 1;
SQL

Checking query status...
