JGonera has submitted this change and it was merged.
Change subject: (Story 743) Monthly charts
......................................................................
(Story 743) Monthly charts
* Make unique uploaders, total uploads and deleted upload show monthly
data
* Separate monthly graphs from daily graphs (separate tabs)
Change-Id: Ide5e049be0fb4dad3349d34cca1df0b328c73eae
---
M dashboards/reportcard.json
D datasources/30-day-uploads.json
M datasources/deleted-uploads.json
A datasources/month-uploads.json
M datasources/unique-uploaders.json
M graphs/deleted-uploads.json
R graphs/month-uploads.json
M graphs/unique-uploaders.json
D mobile/30-day-uploads.sql
M mobile/config.yaml
M mobile/deleted-uploads.py
A mobile/month-uploads.sql
M mobile/unique-uploaders.sql
13 files changed, 236 insertions(+), 325 deletions(-)
Approvals:
JGonera: Verified; Looks good to me, approved
diff --git a/dashboards/reportcard.json b/dashboards/reportcard.json
index 9b3b36e..4c6e710 100644
--- a/dashboards/reportcard.json
+++ b/dashboards/reportcard.json
@@ -4,15 +4,20 @@
"subhead" : "Apps & Web",
"tabs": [
{
- "name": "Uploads",
+ "name": "Uploads monthly",
"graph_ids": [
"unique-uploaders",
- "30-day-uploads",
+ "month-uploads",
+ "deleted-uploads"
+ ]
+ },
+ {
+ "name": "Uploads daily",
+ "graph_ids": [
"successful-uploads",
- "deleted-uploads",
+ "error-uploads",
"no-cats",
"successful-logins",
- "error-uploads",
"share-attempts"
]
},
diff --git a/datasources/30-day-uploads.json b/datasources/30-day-uploads.json
deleted file mode 100644
index cd3092a..0000000
--- a/datasources/30-day-uploads.json
+++ /dev/null
@@ -1,38 +0,0 @@
-{
- "name": "Total uploads (over last 30 days)",
- "timespan": {
- "start": "2013/02/18",
- "step": "1d",
- "end": "2013/03/19"
- },
- "url":
"http://stat1001.wikimedia.org/limn-public-data/mobile/datafiles/30-day-uploads.csv";,
- "notes": "",
- "format": "csv",
- "slug": null,
- "shortName": "30-day-uploads",
- "type": "timeseries",
- "id": "30-day-uploads",
- "columns": [
- {
- "type": "date",
- "label": "date"
- },
- {
- "type": "int",
- "label": "Total"
- },
- {
- "type": "int",
- "label": "Android"
- },
- {
- "type": "int",
- "label": "iOS"
- },
- {
- "type": "int",
- "label": "Web"
- }
- ],
- "desc": ""
-}
diff --git a/datasources/deleted-uploads.json b/datasources/deleted-uploads.json
index e1e5bfe..32bf497 100644
--- a/datasources/deleted-uploads.json
+++ b/datasources/deleted-uploads.json
@@ -1,21 +1,14 @@
{
- "name": "Deleted Uploads per day",
- "timespan": {
- "start": "2013/02/18",
- "step": "1d",
- "end": "2013/03/19"
- },
+ "name": "Uploads deleted (per month)",
"url":
"http://stat1001.wikimedia.org/limn-public-data/mobile/datafiles/deleted-uploads.csv";,
- "notes": "",
"format": "csv",
- "slug": null,
"shortName": "deleted-uploads",
"type": "timeseries",
"id": "deleted-uploads",
"columns": [
{
"type": "date",
- "label": "date"
+ "label": "Month"
},
{
"type": "int",
@@ -29,6 +22,5 @@
"type": "int",
"label": "Web"
}
- ],
- "desc": ""
+ ]
}
diff --git a/datasources/month-uploads.json b/datasources/month-uploads.json
new file mode 100644
index 0000000..6b82a99
--- /dev/null
+++ b/datasources/month-uploads.json
@@ -0,0 +1,26 @@
+{
+ "name": "Uploads (per month)",
+ "url":
"http://stat1001.wikimedia.org/limn-public-data/mobile/datafiles/month-uploads.csv";,
+ "format": "csv",
+ "shortName": "month-uploads",
+ "type": "timeseries",
+ "id": "month-uploads",
+ "columns": [
+ {
+ "type": "date",
+ "label": "Month"
+ },
+ {
+ "type": "int",
+ "label": "Android"
+ },
+ {
+ "type": "int",
+ "label": "iOS"
+ },
+ {
+ "type": "int",
+ "label": "Web"
+ }
+ ]
+}
diff --git a/datasources/unique-uploaders.json
b/datasources/unique-uploaders.json
index a4f4d10..fcc5ab8 100644
--- a/datasources/unique-uploaders.json
+++ b/datasources/unique-uploaders.json
@@ -1,25 +1,14 @@
{
- "name": "Unique uploaders (over last 30 days)",
- "timespan": {
- "start": "2013/02/18",
- "step": "1d",
- "end": "2013/03/19"
- },
+ "name": "Unique uploaders (per month)",
"url":
"http://stat1001.wikimedia.org/limn-public-data/mobile/datafiles/unique-uploaders.csv";,
- "notes": "",
"format": "csv",
- "slug": null,
"shortName": "unique-uploaders",
"type": "timeseries",
"id": "unique-uploaders",
"columns": [
{
"type": "date",
- "label": "date"
- },
- {
- "type": "int",
- "label": "Total"
+ "label": "Month"
},
{
"type": "int",
@@ -33,6 +22,5 @@
"type": "int",
"label": "Web"
}
- ],
- "desc": ""
+ ]
}
diff --git a/graphs/deleted-uploads.json b/graphs/deleted-uploads.json
index b63c535..23f8333 100644
--- a/graphs/deleted-uploads.json
+++ b/graphs/deleted-uploads.json
@@ -1,17 +1,22 @@
{
"graph_version": "0.6.0",
- "name": "Uploads deleted every day",
- "notes": "",
+ "name": "Uploads deleted (per month)",
"slug": "deleted-uploads",
- "shortName": "",
"root": {
- "scaling": "linear",
"nodeType": "canvas",
"minWidth": 750,
"minHeight": 500,
"height": 500,
- "disabled": false,
"width": "auto",
+ "x": {
+ "padding": 0.001
+ },
+ "y": {
+ "domain": [
+ 0,
+ 5000
+ ]
+ },
"children": [
{
"disabled": false,
@@ -81,31 +86,42 @@
},
{
"disabled": false,
- "nodeType": "line-group",
+ "nodeType": "bar-group",
"options": {
"palette": "wmf_projects",
"scale": "log",
- "stroke": {
- "opacity": 1,
- "width": 2
- },
+ "stack" : {
+ "enabled": true
+ },
"dateFormat": "MMM YYYY"
},
"children": [
+ {
+ "disabled": false,
+ "index": 0,
+ "metric": {
+ "source_id": "deleted-uploads",
+ "type": "int",
+ "source_col": 1
+ },
+ "nodeType": "bar",
+ "options": {
+ "label": "Android",
+ "noLegend": false,
+ "dateFormat": "MMM YYYY"
+ }
+ },
{
"disabled": false,
"index": 1,
"metric": {
"source_id": "deleted-uploads",
"type": "int",
- "source_col": 1
+ "source_col": 2
},
- "nodeType": "line",
+ "nodeType": "bar",
"options": {
- "stroke": {
- "width": 2
- },
- "label": "Android",
+ "label": "iOS",
"noLegend": false,
"dateFormat": "MMM YYYY"
}
@@ -116,31 +132,10 @@
"metric": {
"source_id": "deleted-uploads",
"type": "int",
- "source_col": 2
- },
- "nodeType": "line",
- "options": {
- "stroke": {
- "width": 2
- },
- "label": "iOS",
- "noLegend": false,
- "dateFormat": "MMM YYYY"
- }
- },
- {
- "disabled": false,
- "index": 3,
- "metric": {
- "source_id": "deleted-uploads",
- "type": "int",
"source_col": 3
},
- "nodeType": "line",
+ "nodeType": "bar",
"options": {
- "stroke": {
- "width": 2
- },
"label": "Web",
"noLegend": false,
"dateFormat": "MMM YYYY"
@@ -150,6 +145,5 @@
}
]
},
- "id": "deleted-uploads",
- "desc": ""
+ "id": "deleted-uploads"
}
diff --git a/graphs/30-day-uploads.json b/graphs/month-uploads.json
similarity index 69%
rename from graphs/30-day-uploads.json
rename to graphs/month-uploads.json
index d7cf870..54fc939 100644
--- a/graphs/30-day-uploads.json
+++ b/graphs/month-uploads.json
@@ -1,17 +1,22 @@
{
"graph_version": "0.6.0",
- "name": "Total uploads (over last 30 days)",
- "notes": "",
- "slug": "30-day-uploads",
- "shortName": "",
+ "name": "Uploads (per month)",
+ "slug": "month-uploads",
"root": {
- "scaling": "linear",
"nodeType": "canvas",
"minWidth": 750,
"minHeight": 500,
"height": 500,
- "disabled": false,
"width": "auto",
+ "x": {
+ "padding": 0.001
+ },
+ "y": {
+ "domain": [
+ 0,
+ 8000
+ ]
+ },
"children": [
{
"disabled": false,
@@ -81,14 +86,13 @@
},
{
"disabled": false,
- "nodeType": "line-group",
+ "nodeType": "bar-group",
"options": {
"palette": "wmf_projects",
"scale": "log",
- "stroke": {
- "opacity": 1,
- "width": 2
- },
+ "stack" : {
+ "enabled": true
+ },
"dateFormat": "MMM YYYY"
},
"children": [
@@ -96,16 +100,13 @@
"disabled": false,
"index": 0,
"metric": {
- "source_id": "30-day-uploads",
+ "source_id": "month-uploads",
"type": "int",
"source_col": 1
},
- "nodeType": "line",
+ "nodeType": "bar",
"options": {
- "stroke": {
- "width": 2
- },
- "label": "Total",
+ "label": "Android",
"noLegend": false,
"dateFormat": "MMM YYYY"
}
@@ -114,16 +115,13 @@
"disabled": false,
"index": 1,
"metric": {
- "source_id": "30-day-uploads",
+ "source_id": "month-uploads",
"type": "int",
"source_col": 2
},
- "nodeType": "line",
+ "nodeType": "bar",
"options": {
- "stroke": {
- "width": 2
- },
- "label": "Android",
+ "label": "iOS",
"noLegend": false,
"dateFormat": "MMM YYYY"
}
@@ -132,33 +130,12 @@
"disabled": false,
"index": 2,
"metric": {
- "source_id": "30-day-uploads",
+ "source_id": "month-uploads",
"type": "int",
"source_col": 3
},
- "nodeType": "line",
+ "nodeType": "bar",
"options": {
- "stroke": {
- "width": 2
- },
- "label": "iOS",
- "noLegend": false,
- "dateFormat": "MMM YYYY"
- }
- },
- {
- "disabled": false,
- "index": 3,
- "metric": {
- "source_id": "30-day-uploads",
- "type": "int",
- "source_col": 4
- },
- "nodeType": "line",
- "options": {
- "stroke": {
- "width": 2
- },
"label": "Web",
"noLegend": false,
"dateFormat": "MMM YYYY"
@@ -168,6 +145,5 @@
}
]
},
- "id": "30-day-uploads",
- "desc": ""
+ "id": "month-uploads"
}
diff --git a/graphs/unique-uploaders.json b/graphs/unique-uploaders.json
index a7c469a..efb263d 100644
--- a/graphs/unique-uploaders.json
+++ b/graphs/unique-uploaders.json
@@ -1,17 +1,22 @@
{
"graph_version": "0.6.0",
- "name": "Unique uploaders (over last 30 days)",
- "notes": "",
+ "name": "Unique uploaders (per month)",
"slug": "unique-uploaders",
- "shortName": "",
"root": {
- "scaling": "linear",
"nodeType": "canvas",
"minWidth": 750,
"minHeight": 500,
"height": 500,
- "disabled": false,
"width": "auto",
+ "x": {
+ "padding": 0.001
+ },
+ "y": {
+ "domain": [
+ 0,
+ 5000
+ ]
+ },
"children": [
{
"disabled": false,
@@ -81,14 +86,13 @@
},
{
"disabled": false,
- "nodeType": "line-group",
+ "nodeType": "bar-group",
"options": {
"palette": "wmf_projects",
"scale": "log",
- "stroke": {
- "opacity": 1,
- "width": 2
- },
+ "stack" : {
+ "enabled": true
+ },
"dateFormat": "MMM YYYY"
},
"children": [
@@ -100,12 +104,9 @@
"type": "int",
"source_col": 1
},
- "nodeType": "line",
+ "nodeType": "bar",
"options": {
- "stroke": {
- "width": 2
- },
- "label": "Total",
+ "label": "Android",
"noLegend": false,
"dateFormat": "MMM YYYY"
}
@@ -118,12 +119,9 @@
"type": "int",
"source_col": 2
},
- "nodeType": "line",
+ "nodeType": "bar",
"options": {
- "stroke": {
- "width": 2
- },
- "label": "Android",
+ "label": "iOS",
"noLegend": false,
"dateFormat": "MMM YYYY"
}
@@ -136,29 +134,8 @@
"type": "int",
"source_col": 3
},
- "nodeType": "line",
+ "nodeType": "bar",
"options": {
- "stroke": {
- "width": 2
- },
- "label": "iOS",
- "noLegend": false,
- "dateFormat": "MMM YYYY"
- }
- },
- {
- "disabled": false,
- "index": 3,
- "metric": {
- "source_id": "unique-uploaders",
- "type": "int",
- "source_col": 4
- },
- "nodeType": "line",
- "options": {
- "stroke": {
- "width": 2
- },
"label": "Web",
"noLegend": false,
"dateFormat": "MMM YYYY"
@@ -168,6 +145,5 @@
}
]
},
- "id": "unique-uploaders",
- "desc": ""
+ "id": "unique-uploaders"
}
diff --git a/mobile/30-day-uploads.sql b/mobile/30-day-uploads.sql
deleted file mode 100644
index 53427fe..0000000
--- a/mobile/30-day-uploads.sql
+++ /dev/null
@@ -1,49 +0,0 @@
-SELECT
- Date,
-
- (
- (SELECT COUNT(*) FROM {{ tables.upload_attempts }} WHERE
- event_result = 'success' AND
- wiki = 'commonswiki' AND
- DATE(timestamp) < Date AND
- (timestamp + INTERVAL 0 DAY) > (Date - INTERVAL {{
intervals.running_average }} DAY)
- ) +
- (SELECT COUNT(*) FROM {{ tables.upload_web }} WHERE
- event_action = 'success' AND
- wiki != 'testwiki' AND
- DATE(timestamp) < Date AND
- (timestamp + INTERVAL 0 DAY) > (Date - INTERVAL {{
intervals.running_average }} DAY)
- )
- ) AS Total,
-
- (SELECT COUNT(*) FROM {{ tables.upload_attempts }} WHERE
- event_platform LIKE 'Android%' AND
- event_result = 'success' AND
- wiki = 'commonswiki' AND
- DATE(timestamp) < Date AND
- (timestamp + INTERVAL 0 DAY) > (Date - INTERVAL {{
intervals.running_average }} DAY)
- ) AS Android,
-
- (SELECT COUNT(*) FROM {{ tables.upload_attempts }} WHERE
- event_platform LIKE 'iOS%' AND
- event_result = 'success' AND
- wiki = 'commonswiki' AND
- DATE(timestamp) < Date AND
- (timestamp + INTERVAL 0 DAY) > (Date - INTERVAL {{
intervals.running_average }} DAY)
- ) AS iOS,
-
- (SELECT COUNT(*) FROM {{ tables.upload_web }} WHERE
- event_action = 'success' AND
- wiki != 'testwiki' AND
- DATE(timestamp) < Date AND
- (timestamp + INTERVAL 0 DAY) > (Date - INTERVAL {{
intervals.running_average }} DAY)
- ) AS Web
-
--- http://stackoverflow.com/a/6871220/365238
-FROM (
- SELECT DATE_FORMAT(
- ADDDATE(CURDATE() - INTERVAL {{ intervals.running_average }} - 1 DAY,
@num:=@num+1),
- '%Y-%m-%d'
- ) AS Date
- FROM {{ tables.upload_attempts }}, (SELECT @num:=-1) num LIMIT {{
intervals.running_average }}
-) AS Month;
diff --git a/mobile/config.yaml b/mobile/config.yaml
index 3daa6b8..369d6a6 100644
--- a/mobile/config.yaml
+++ b/mobile/config.yaml
@@ -24,9 +24,9 @@
db: el
graphs:
unique-uploaders:
- title: Unique uploaders (over last 30 days)
- 30-day-uploads:
- title: Total uploads (over last 30 days)
+ title: Unique uploaders (per month)
+ month-uploads:
+ title: Uploads (per month)
cancelled-uploads:
title: Uploads cancelled by user (per day)
error-uploads:
@@ -44,7 +44,7 @@
no-cats: # Only puppies
title: Percentage of files with a category added
deleted-uploads:
- title: Mobile uploads deleted per day
+ title: Uploads deleted (per month)
performance-rendering:
title: Rendering time (ms)
diff --git a/mobile/deleted-uploads.py b/mobile/deleted-uploads.py
index 84625af..8122e49 100644
--- a/mobile/deleted-uploads.py
+++ b/mobile/deleted-uploads.py
@@ -22,36 +22,46 @@
deleted_file_template = u"""
SELECT
- Date,
- (SELECT COUNT(*) FROM logging WHERE
- log_namespace = 6 AND
- log_title IN (%s) AND
- DATE(log_timestamp) = Date AND
- log_action = 'delete'
- ) AS Android,
+ DATE_FORMAT(CONCAT(Month.month, '01'), '%%%%Y-%%%%m-%%%%d') AS Month,
+ COALESCE(Android.count, 0) AS Android,
+ COALESCE(iOS.count, 0) AS iOS,
+ COALESCE(Web.count, 0) AS Web
- (SELECT COUNT(*) FROM logging WHERE
- log_namespace = 6 AND
- log_title IN (%s) AND
- DATE(log_timestamp) = Date AND
- log_action = 'delete'
- ) AS iOS,
-
- (SELECT COUNT(*) FROM logging WHERE
- log_namespace = 6 AND
- log_title IN (%s) AND
- DATE(log_timestamp) = Date AND
- log_action = 'delete'
- ) AS Web
-
-- http://stackoverflow.com/a/6871220/365238
FROM (
- SELECT DATE_FORMAT(
- ADDDATE(CURDATE() - INTERVAL {{ intervals.running_average }} - 1 DAY,
@num:=@num+1),
- '%%%%Y-%%%%m-%%%%d'
- ) AS Date
- FROM logging, (SELECT @num:=-1) num LIMIT {{ intervals.running_average }}
-) AS Month;
+ SELECT EXTRACT(YEAR_MONTH FROM SUBDATE(CURDATE(), INTERVAL @num:=@num+1
MONTH)) AS month
+ FROM information_schema.columns, (SELECT @num:=-1) num LIMIT 12
+) AS Month
+
+LEFT JOIN (
+ SELECT EXTRACT(YEAR_MONTH FROM log_timestamp) AS month, COUNT(*) AS count
+ FROM logging
+ WHERE
+ log_namespace = 6 AND
+ log_title IN (%s) AND
+ log_action = 'delete'
+ GROUP BY month
+) AS Android ON Month.month = Android.month
+
+LEFT JOIN (
+ SELECT EXTRACT(YEAR_MONTH FROM log_timestamp) AS month, COUNT(*) AS count
+ FROM logging
+ WHERE
+ log_namespace = 6 AND
+ log_title IN (%s) AND
+ log_action = 'delete'
+ GROUP BY month
+) AS iOS ON Month.month = iOS.month
+
+LEFT JOIN (
+ SELECT EXTRACT(YEAR_MONTH FROM log_timestamp) AS month, COUNT(*) AS count
+ FROM logging
+ WHERE
+ log_namespace = 6 AND
+ log_title IN (%s) AND
+ log_action = 'delete'
+ GROUP BY month
+) AS Web ON Month.month = Web.month;
"""
headers = ["date", "Android", "iOS", "Web"]
diff --git a/mobile/month-uploads.sql b/mobile/month-uploads.sql
new file mode 100644
index 0000000..ac773a0
--- /dev/null
+++ b/mobile/month-uploads.sql
@@ -0,0 +1,40 @@
+SELECT
+ DATE_FORMAT(CONCAT(Month.month, '01'), '%Y-%m-%d') AS Month,
+ COALESCE(Android.count, 0) AS Android,
+ COALESCE(iOS.count, 0) AS iOS,
+ COALESCE(Web.count, 0) AS Web
+
+-- http://stackoverflow.com/a/6871220/365238
+FROM (
+ SELECT EXTRACT(YEAR_MONTH FROM SUBDATE(CURDATE(), INTERVAL @num:=@num+1
MONTH)) AS month
+ FROM information_schema.columns, (SELECT @num:=-1) num LIMIT 12
+) AS Month
+
+LEFT JOIN (
+ SELECT EXTRACT(YEAR_MONTH FROM timestamp) AS month, COUNT(*) AS count
+ FROM {{ tables.upload_attempts }}
+ WHERE
+ event_platform LIKE 'Android%' AND
+ event_result = 'success' AND
+ wiki = 'commonswiki'
+ GROUP BY month
+) AS Android ON Month.month = Android.month
+
+LEFT JOIN (
+ SELECT EXTRACT(YEAR_MONTH FROM timestamp) AS month, COUNT(*) AS count
+ FROM {{ tables.upload_attempts }}
+ WHERE
+ event_platform LIKE 'iOS%' AND
+ event_result = 'success' AND
+ wiki = 'commonswiki'
+ GROUP BY month
+) AS iOS ON Month.month = iOS.month
+
+LEFT JOIN (
+ SELECT EXTRACT(YEAR_MONTH FROM timestamp) AS month, COUNT(*) AS count
+ FROM {{ tables.upload_web }}
+ WHERE
+ event_action = 'success' AND
+ wiki != 'testwiki'
+ GROUP BY month
+) AS Web ON Month.month = Web.month;
diff --git a/mobile/unique-uploaders.sql b/mobile/unique-uploaders.sql
index 334d11c..a0941bb 100644
--- a/mobile/unique-uploaders.sql
+++ b/mobile/unique-uploaders.sql
@@ -1,49 +1,40 @@
SELECT
- Date,
-
- (
- (SELECT COUNT(DISTINCT event_username) FROM {{ tables.upload_attempts
}} WHERE
- event_result = 'success' AND
- wiki = 'commonswiki' AND
- DATE(timestamp) < Date AND
- (timestamp + INTERVAL 0 DAY) > (Date - INTERVAL {{
intervals.running_average }} DAY)
- ) +
- (SELECT COUNT(DISTINCT event_token) FROM {{ tables.upload_web }} WHERE
- event_action = 'success' AND
- wiki != 'testwiki' AND
- DATE(timestamp) < Date AND
- (timestamp + INTERVAL 0 DAY) > (Date - INTERVAL {{
intervals.running_average }} DAY)
- )
- ) AS Total,
-
- (SELECT COUNT(DISTINCT event_username) FROM {{ tables.upload_attempts }}
WHERE
- event_platform LIKE 'Android%' AND
- event_result = 'success' AND
- wiki = 'commonswiki' AND
- DATE(timestamp) < Date AND
- (timestamp + INTERVAL 0 DAY) > (Date - INTERVAL {{
intervals.running_average }} DAY)
- ) AS Android,
-
- (SELECT COUNT(DISTINCT event_username) FROM {{ tables.upload_attempts }}
WHERE
- event_platform LIKE 'iOS%' AND
- event_result = 'success' AND
- wiki = 'commonswiki' AND
- DATE(timestamp) < Date AND
- (timestamp + INTERVAL 0 DAY) > (Date - INTERVAL {{
intervals.running_average }} DAY)
- ) AS iOS,
-
- (SELECT COUNT(DISTINCT event_token) FROM {{ tables.upload_web }} WHERE
- event_action = 'success' AND
- wiki != 'testwiki' AND
- DATE(timestamp) < Date AND
- (timestamp + INTERVAL 0 DAY) > (Date - INTERVAL {{
intervals.running_average }} DAY)
- ) AS Web
+ DATE_FORMAT(CONCAT(Month.month, '01'), '%Y-%m-%d') AS Month,
+ COALESCE(Android.count, 0) AS Android,
+ COALESCE(iOS.count, 0) AS iOS,
+ COALESCE(Web.count, 0) AS Web
-- http://stackoverflow.com/a/6871220/365238
FROM (
- SELECT DATE_FORMAT(
- ADDDATE(CURDATE() - INTERVAL {{ intervals.running_average }} - 1 DAY,
@num:=@num+1),
- '%Y-%m-%d'
- ) AS Date
- FROM {{ tables.upload_attempts }}, (SELECT @num:=-1) num LIMIT {{
intervals.running_average }}
-) AS Month;
+ SELECT EXTRACT(YEAR_MONTH FROM SUBDATE(CURDATE(), INTERVAL @num:=@num+1
MONTH)) AS month
+ FROM information_schema.columns, (SELECT @num:=-1) num LIMIT 12
+) AS Month
+
+LEFT JOIN (
+ SELECT EXTRACT(YEAR_MONTH FROM timestamp) AS month, COUNT(DISTINCT
event_username) AS count
+ FROM {{ tables.upload_attempts }}
+ WHERE
+ event_platform LIKE 'Android%' AND
+ event_result = 'success' AND
+ wiki = 'commonswiki'
+ GROUP BY month
+) AS Android ON Month.month = Android.month
+
+LEFT JOIN (
+ SELECT EXTRACT(YEAR_MONTH FROM timestamp) AS month, COUNT(DISTINCT
event_username) AS count
+ FROM {{ tables.upload_attempts }}
+ WHERE
+ event_platform LIKE 'iOS%' AND
+ event_result = 'success' AND
+ wiki = 'commonswiki'
+ GROUP BY month
+) AS iOS ON Month.month = iOS.month
+
+LEFT JOIN (
+ SELECT EXTRACT(YEAR_MONTH FROM timestamp) AS month, COUNT(DISTINCT
event_username) AS count
+ FROM {{ tables.upload_web }}
+ WHERE
+ event_action = 'success' AND
+ wiki != 'testwiki'
+ GROUP BY month
+) AS Web ON Month.month = Web.month;
--
To view, visit https://gerrit.wikimedia.org/r/65326
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: Ide5e049be0fb4dad3349d34cca1df0b328c73eae
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-mobile-data
Gerrit-Branch: master
Gerrit-Owner: JGonera <[email protected]>
Gerrit-Reviewer: JGonera <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
