Edit counts by user, month, tag group

User:Lee Vilenski's edits, broken down by month, and by whether they have a tag with "mobile" in their name (currently those are "mobile edit", "mobile web edit", "mobile app edit", "advanced mobile edit", and "mobile").

For [[WP:HD#Tool for mobile edits]] circa 9 August 2023.

SQL

SELECT CONCAT(LEFT(rev_timestamp, 4), '-', SUBSTR(rev_timestamp, 5, 2)) AS 'yyyy-mm',
       COUNT(mobile) AS 'mobile edits',
       COUNT(*) - COUNT(mobile) AS 'nonmobile edits',
       COUNT(*) AS 'total edits',
       100 * COUNT(mobile) / COUNT(*) AS 'percent mobile'
FROM (SELECT DISTINCT rev_id,
                      rev_timestamp,
		      CASE WHEN ct_id IS NOT NULL THEN 1 ELSE NULL END AS mobile
      FROM revision_userindex
      JOIN actor_revision ON actor_id = rev_actor
      LEFT JOIN change_tag ON ct_rev_id = rev_id
                          AND ct_tag_id IN (SELECT ctd_id
                                            FROM change_tag_def
                                            WHERE ctd_name LIKE '%mobile%')
      WHERE actor_name = 'Lee Vilenski') sq
GROUP BY LEFT(rev_timestamp, 6);

SQL

Checking query status...