jenkins-bot has submitted this change and it was merged.
Change subject: Adding new datasource for new active editors for mobile limn
graphs
......................................................................
Adding new datasource for new active editors for mobile limn graphs
Change-Id: I92305cad3a78a50b7f2891db1446d20faddc3f34
---
A datasources/edits-monthly-new-active.json
A mobile/edits-monthly-new-active.sql
2 files changed, 65 insertions(+), 0 deletions(-)
Approvals:
Kaldari: Looks good to me, approved
jenkins-bot: Verified
diff --git a/datasources/edits-monthly-new-active.json
b/datasources/edits-monthly-new-active.json
new file mode 100644
index 0000000..061e9cf
--- /dev/null
+++ b/datasources/edits-monthly-new-active.json
@@ -0,0 +1,22 @@
+{
+ "name": "Editors who reached 5 edits on enwiki within 30 days of
registering",
+ "url":
"http://stat1001.wikimedia.org/limn-public-data/mobile/datafiles/edits-monthly-new-active.csv";,
+ "format": "csv",
+ "shortName": "edits-monthly-new-active",
+ "type": "timeseries",
+ "id": "edits-monthly-new-active",
+ "columns": [
+ {
+ "type": "date",
+ "label": "Month"
+ },
+ {
+ "type": "int",
+ "label": "Mobile"
+ },
+ {
+ "type": "int",
+ "label": "Desktop"
+ }
+ ]
+}
diff --git a/mobile/edits-monthly-new-active.sql
b/mobile/edits-monthly-new-active.sql
new file mode 100644
index 0000000..b53f3ed
--- /dev/null
+++ b/mobile/edits-monthly-new-active.sql
@@ -0,0 +1,43 @@
+SET @n = 5; /* edits threshold */
+SET @u = 30; /* activity unit in days */
+
+select sum(if(event_displaymobile = 1, 1, 0)) as mobile,
+ sum(if(event_displaymobile <> 1, 1, 0)) as desktop
+ from (SELECT user_id
+ FROM (SELECT rev_user as user_id,
+ count(*) AS revisions
+ FROM enwiki.revision
+ WHERE rev_timestamp BETWEEN
DATE_FORMAT(DATE_SUB('{to_timestamp}', INTERVAL @u DAY), "%Y%m%d%H%i%S") AND
'{to_timestamp}'
+ AND rev_user in (select log_user
+ from enwiki.logging
+ where log_type = 'newusers'
+ and log_action = 'create'
+ and log_timestamp BETWEEN
DATE_FORMAT(DATE_SUB('{to_timestamp}', INTERVAL @u DAY), "%Y%m%d%H%i%S") AND
'{to_timestamp}'
+ )
+ GROUP BY rev_user
+
+ UNION ALL
+
+ SELECT ar_user as user_id,
+ count(*) AS revisions
+ FROM enwiki.archive
+ WHERE ar_timestamp BETWEEN
DATE_FORMAT(DATE_SUB('{to_timestamp}', INTERVAL @u DAY), "%Y%m%d%H%i%S") AND
'{to_timestamp}'
+ AND ar_user in (select log_user
+ from enwiki.logging
+ where log_type = 'newusers'
+ and log_action = 'create'
+ and log_timestamp BETWEEN
DATE_FORMAT(DATE_SUB('{to_timestamp}', INTERVAL @u DAY), "%Y%m%d%H%i%S") AND
'{to_timestamp}'
+ )
+ GROUP BY ar_user
+
+ ) AS user_revisions
+ GROUP BY user_id
+ HAVING SUM(revisions) >= @n
+ ) as rolling_active_editors
+ inner join
+ log.ServerSideAccountCreation_5487345 on event_userId =
rolling_active_editors.user_id
+ AND useragent NOT LIKE
"%WikipediaApp%"
+ AND wiki = "enwiki"
+ and timestamp between
DATE_FORMAT(DATE_SUB('{to_timestamp}', INTERVAL @u+1 DAY), "%Y%m%d%H%i%S")
+ AND
date_format(date_add('{to_timestamp}', interval 1 day), '%Y%m%d%H%i%S')
+;
--
To view, visit https://gerrit.wikimedia.org/r/166401
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I92305cad3a78a50b7f2891db1446d20faddc3f34
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-mobile-data
Gerrit-Branch: master
Gerrit-Owner: Kaldari <rkald...@wikimedia.org>
Gerrit-Reviewer: Kaldari <rkald...@wikimedia.org>
Gerrit-Reviewer: jenkins-bot <>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
