SELECT
  date, platform,
  COUNT(1) AS n_serps,
  COUNT(DISTINCT(uuid)) AS n_users
FROM (
  SELECT
    TO_DATE(ts) AS date,
    access_method AS platform,
    CONCAT(client_ip, user_agent, accept_language) AS uuid
  FROM wmf.webrequest
  WHERE
    webrequest_source IN('text')
    AND year = 2016 AND ((month = 10 AND day < 7) OR (month = 9 AND day > 20))
    AND http_status IN('200', '304')
    AND INSTR(uri_path, 'index.php') > 0
    AND INSTR(uri_query, '?search=') > 0
) AS webrequest_subset
GROUP BY date, platform;

library(tidyverse)

requests <- read_csv("search_desktop-vs-mobile.csv", col_types = "Dcii")

requests %>%
  group_by(platform) %>%
  summarize(`median SERPs a day` = polloi::compress(median(n_serps)),
            `median users a day` = polloi::compress(median(n_users))) %>%
  knitr::kable(format = "markdown", align = c("l", "r", "r"))

ggplot(requests, aes(x = date, y = n_serps/1e6, color = platform)) +
  geom_line() +
  geom_point() +
  scale_x_date(date_labels = "%a\n%d %b", date_breaks = "2 days") +
  theme_minimal() +
  labs(x = "Date", y = "SERPs (in millions)",
       title = "Number of search engine result pages (SERPs) by platform") +
  theme(legend.position = "bottom")

ggplot(requests, aes(x = date, y = n_users, color = platform)) +
  geom_line() +
  geom_point() +
  scale_y_continuous(labels = polloi::compress, breaks = seq(0, 1.2e6, 1e5)) +
  scale_x_date(date_labels = "%a\n%d %b", date_breaks = "2 days") +
  theme_minimal() +
  labs(x = "Date", y = "Users",
       title = "Number of unique search users by platform",
       subtitle = "Identifying a user by their user agent + IP address + Accept-Language tuple") +
  theme(legend.position = "bottom")