Wikidata description editing activity with Wikipedia Android app

SQL

USE wikidatawiki_p;
SELECT
  IF(n_edits >= 5, '5 or more', 'fewer than 5') AS wikidata_description_edits,
  COUNT(1) AS n_android_app_users
FROM (
  SELECT
    rev_user,
    SUM(
      CASE WHEN (rev_timestamp < '20180710' AND ct_tag_id = 13) THEN 1
           -- ^ wikidata description edits tagged generically as 'mobile app edit'
           --   can be safely assumed to come from android
           WHEN (rev_timestamp >= '20180710' AND ct_tag_id = 14) THEN 1
           -- ^ wikidata description edits made on android as of 10 July 2018 are
           --   explicitly tagged as 'android app edit'
           ELSE 0 END
           -- ^ 'mobile app edit'-tagged wikidata description edits made later may
           --   come from iOS since Fall 2018
    ) AS n_edits
  FROM (
    SELECT rev_id, rev_user, rev_timestamp
    FROM (
      SELECT rev_id, rev_user, rev_timestamp, rev_comment, rev_comment_temp
      FROM revision
      LEFT JOIN (
        -- Temporary table creating a relation between each row of the revision
        -- table and a row of the comment table. It has been created to avoid
        -- blocking on an alter of the revision table.
        SELECT revcomment_rev AS rev_id_temp, comment_text AS rev_comment_temp
        FROM revision_comment_temp AS rct
        LEFT JOIN `comment` ON rct.revcomment_comment_id = `comment`.comment_id
        -- cf. https://www.mediawiki.org/wiki/Manual:Revision_comment_temp_table
      ) AS temporary_rev_comments ON revision.rev_id = temporary_rev_comments.rev_id_temp
    ) AS full_revision_comments
    WHERE rev_timestamp >= '20160901' -- WD desc editing on android started in 9/16
      AND (
        INSTR(rev_comment, 'wbsetdescription') > 0
        OR INSTR(rev_comment_temp, 'wbsetdescription') > 0 
      )
  ) AS description_edits
  INNER JOIN (
    SELECT ct_rev_id, ct_tag_id
    FROM change_tag
    WHERE ct_tag_id IN(13, 14) -- on wikidatawiki: 13 = android app, 14 = mobile app
  ) AS android_edits ON description_edits.rev_id = android_edits.ct_rev_id
  GROUP BY rev_user
) AS android_app_users_wd_desc_edit_counts
GROUP BY wikidata_description_edits;
SQL

Checking query status...
