SELECT
  DATE(timestamp) AS date,
  COUNT(*) AS `clicks on other projects`,
  SUM(IF(INSTR(event_destination, 'itunes.apple.com') > 0, 1, 0)) AS `iOS app link clicks`,
  SUM(IF(INSTR(event_destination, 'play.google.com') > 0, 1, 0)) AS `Android app link clicks`
FROM WikipediaPortal_15890769
WHERE
  LEFT(timestamp, 8) > '20161030'
  AND event_cohort = 'baseline'
  AND event_event_type = 'clickthrough'
  AND event_section_used = 'other projects'
GROUP BY date;

+------------+--------------------------+---------------------+-------------------------+
| date       | clicks on other projects | iOS app link clicks | Android app link clicks |
+------------+--------------------------+---------------------+-------------------------+
| 2016-10-31 |                       68 |                   0 |                       0 |
| 2016-11-01 |                       74 |                   1 |                       0 |
| 2016-11-02 |                       34 |                   4 |                       3 |
+------------+--------------------------+---------------------+-------------------------+
3 rows in set (1.02 sec)

SELECT
  DATE(timestamp) AS date,
  COUNT(*) AS `clicks on other projects`,
  SUM(IF(INSTR(event_destination, 'itunes.apple.com') > 0, 1, 0)) AS `iOS app link clicks`,
  SUM(IF(INSTR(event_destination, 'play.google.com') > 0, 1, 0)) AS `Android app link clicks`,
  SUM(IF(INSTR(event_destination, '//en.wikipedia.org/wiki/List_of_Wikipedia_mobile_applications') > 0, 1, 0)) AS `Clicks on app list` 
FROM WikipediaPortal_15890769
WHERE
  LEFT(timestamp, 8) > '20161030'
  AND event_cohort = 'baseline'
  AND event_event_type = 'clickthrough'
  AND event_section_used = 'other projects'
GROUP BY date;

SELECT
  TO_DATE(ts) AS date,
  access_method,
  SUM(IF(referer = 'https://www.wikipedia.org/' OR referer = 'http://www.wikipedia.org/', 1, 0)) AS pvs_from_portal,
  SUM(IF(referer = 'https://www.wikipedia.org/' OR referer = 'http://www.wikipedia.org/', 0, 1)) AS pvs_from_elsewhere
FROM webrequest
WHERE
  webrequest_source = 'text'
  AND year = 2016 AND ((month = 10 AND day > 20) OR (month = 11 AND day > 0))
  AND is_pageview
  AND pageview_info['project'] = 'en.wikipedia'
  AND pageview_info['page_title'] = 'List_of_Wikipedia_mobile_applications'
GROUP BY TO_DATE(ts), access_method;

library(tidyverse)

pageviews <- read_csv("~/Desktop/T146807.csv", col_types = "Dcii") %>%
  arrange(date, access_method) %>%
  rename(Date = date,
         `Access Method` = access_method,
         `Wikipedia.org Portal` = pvs_from_portal,
         `Elsewhere or direct` = pvs_from_elsewhere) %>%
  gather(From, Pageviews, -c(Date, `Access Method`))

ggplot(filter(pageviews, `Access Method` != "mobile app"),
              aes(x = Date, y = Pageviews, color = From)) +
  geom_line() +
  geom_vline(xintercept = as.numeric(as.Date("2016-11-01")),
             linetype = "dashed", color = "gray50") +
  geom_point() +
  facet_wrap(~ `Access Method`, nrow = 3) +
  theme_minimal(base_family = "Open Sans") +
  labs(title = "\"List of Wikipedia mobile applications\" pageviews",
       caption = "Dashed line indicates deployment of Wikipedia.org Portal patch to add app links") +
  theme(legend.position = "bottom")

SELECT
  DATE(timestamp) AS date,
  COUNT(*) AS `clicks on other (sister) projects`,
  SUM(IF(INSTR(event_destination, 'itunes.apple.com') > 0, 1, 0)) AS `iOS app link clicks`,
  SUM(IF(INSTR(event_destination, 'play.google.com') > 0, 1, 0)) AS `Android app link clicks`,
  SUM(IF(event_destination = 'https://en.wikipedia.org/wiki/List_of_Wikipedia_mobile_applications', 1, 0)) AS `Available apps list clicks`
FROM WikipediaPortal_15890769
WHERE
  LEFT(timestamp, 8) > '20161030'
  AND event_cohort = 'baseline'
  AND event_event_type = 'clickthrough'
  AND event_section_used = 'other projects'
GROUP BY date;

SELECT
  DATE(LEFT(timestamp, 8)) AS `date`,
  CASE WHEN (
         INSTR(userAgent, 'Android') > 0
         OR INSTR(userAgent, 'Mobile') > 0
         OR INSTR(userAgent, 'iOS') > 0
         OR INSTR(userAgent, 'Phone') > 0
       ) THEN 'Mobile'
       ELSE 'Desktop' END AS device,
  CASE WHEN INSTR(event_destination, 'itunes.apple.com') > 0 THEN 'iOS App link'
       WHEN INSTR(event_destination, 'play.google.com') > 0 THEN 'Android app link'
       ELSE 'List of apps' END AS clicked,
  COUNT(*) AS clicks
FROM WikipediaPortal_15890769
WHERE
  LEFT(timestamp, 8) >= ${START_DATE} AND LEFT(timestamp, 8) <= ${END_DATE}
  -- ^ or whatever date-subsetting clause you want
  AND event_cohort = 'baseline'
  AND event_event_type = 'clickthrough'
  AND event_section_used = 'other projects'
  AND (
    INSTR(event_destination, 'itunes.apple.com') > 0
    OR INSTR(event_destination, 'play.google.com') > 0
    OR event_destination = 'https://en.wikipedia.org/wiki/List_of_Wikipedia_mobile_applications'
  )
GROUP BY `date`, device, clicked;