iOS edits per editor
ActivePublic
Actions

Authored by • chelsyx on Aug 1 2018, 11:07 PM.

Tags

None

Referenced Files

	F24413187: iOS edits per editor
	Aug 1 2018, 11:07 PM

Subscribers

None

	-- Definition: For any given time period (monthly/quarterly), of the non-bot users that completed at least 1 edit via the iOS app, how many edits did they complete on average (arithmetic mean/median). Edits on other platform won’t affect this metric.
	-- Here we only counts the number of edits per editor without doing any aggregation, since we want to see the distribution before determine whether we should use mean or median, or other aggregation metrics.
	SELECT month,
	local_user_id,
	IFNULL(user_name, '') AS user_name,
	IFNULL(SUM(edits), 0) AS edits,
	IFNULL(SUM(edits * deleted), 0) AS deleted_edits
	FROM (

	SELECT
	LEFT(rev_timestamp, 6) AS `month`,
	rev_user AS `local_user_id`,
	COUNT(*) AS `edits`,
	0 AS `deleted`
	FROM revision
	INNER JOIN tag_summary ON rev_id = ts_rev_id AND ts_tags LIKE '%ios app edit%'
	WHERE LEFT(rev_timestamp, 6) = '201807' -- time restriction here
	GROUP BY LEFT(rev_timestamp, 6), rev_user

	UNION ALL

	SELECT
	LEFT(ar_timestamp, 6) AS `month`,
	ar_user AS `local_user_id`,
	COUNT(*) AS `edits`,
	1 AS `deleted`
	FROM archive
	INNER JOIN tag_summary ON ar_rev_id = ts_rev_id AND ts_tags LIKE '%ios app edit%'
	WHERE LEFT(ar_timestamp, 6) = '201807' -- time restriction here
	GROUP BY LEFT(ar_timestamp, 6), ar_user

	) AS edit_per_editor
	LEFT JOIN user ON local_user_id = user_id
	WHERE local_user_id NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot')
	AND local_user_id NOT IN (SELECT ufg_user FROM user_former_groups WHERE ufg_group = 'bot')
	GROUP BY month, local_user_id

iOS edits per editorActivePublicActions