New editors - Wikidata

SQL

SET @t = 1; /* time cutoff in days */
SET @n = 1; /* edits threshold */
SET @start_date = "20141001"; 
SET @end_date = "20141201"; 
 
/* Results in a set of "new editors" */
SELECT
DATE(user_registration) AS date,
COUNT(user_id) AS new_editors
FROM
(
SELECT
  user_id,
  user_name,
  user_registration
FROM
  (
    /* Get revisions to content pages that are still visible */
    SELECT
      user_id,
      user_name,
      user_registration,
      SUM(rev_id IS NOT NULL) AS revisions
    FROM wikidatawiki_p.user
    INNER JOIN wikidatawiki_p.logging ON /* Filter users not created manually */
      log_user = user_id AND
      log_type = "newusers" AND
      log_action = "create"
    LEFT JOIN wikidatawiki_p.revision ON
        rev_user = user_id AND
        rev_timestamp <= DATE_FORMAT(
            DATE_ADD(user_registration, INTERVAL @t DAY),
            '%Y%m%d%H%i%S')
    WHERE user_registration BETWEEN @start_date AND @end_date
    GROUP BY 1,2,3
 
    UNION ALL
 
    /* Get revisions to content pages that have been archived */
    SELECT
      user_id,
      user_name,
      user_registration,
      SUM(ar_id IS NOT NULL) AS revisions /* Note that ar_rev_id is sometimes set to NULL :( */
    FROM wikidatawiki_p.user
    INNER JOIN wikidatawiki_p.logging ON /* Filter users not created manually */
      log_user = user_id AND
      log_type = "newusers" AND
      log_action = "create"
    LEFT JOIN wikidatawiki_p.archive ON 
      ar_user = user_id AND
      ar_timestamp <= DATE_FORMAT(
          DATE_ADD(user_registration, INTERVAL @t DAY),
          '%Y%m%d%H%i%S')
    WHERE user_registration BETWEEN @start_date AND @end_date
    GROUP BY 1,2,3
  ) AS user_content_revision_count
GROUP BY 1,2,3
HAVING SUM(revisions) >= @n
) AS new_editors;
SQL

Checking query status...
