ADD JAR hdfs:///wmf/refinery/current/artifacts/refinery-hive.jar;
CREATE TEMPORARY FUNCTION array_sum AS 'org.wikimedia.analytics.refinery.hive.ArraySumUDF';
CREATE TEMPORARY FUNCTION is_spider as 'org.wikimedia.analytics.refinery.hive.IsSpiderUDF';

SELECT
  year, month, day,
  IF(source = 'web', 'desktop or mobile web', 'mobile app') AS platform,
  array_sum(requests.hitstotal, -1) = 0 AS zero_result,
  COUNT(1) AS searches
FROM cirrussearchrequestset
WHERE wikiid = 'hiwiki'
  AND year = 2018 AND month = 3
  AND (
    (source = 'api' AND useragent RLIKE '^WikipediaApp')
    OR source = 'web'
  )
  AND requests[SIZE(requests)-1].querytype IN('comp_suggest', 'full_text', 'GeoData_spatial_search', 'prefix', 'more_like', 'regex')
  AND NOT ARRAY_CONTAINS(requests.hitstotal, -1)
  AND NOT is_spider(useragent)
GROUP BY
  year, month, day,
  IF(source = 'web', 'desktop or mobile web', 'mobile app'),
  array_sum(requests.hitstotal, -1) = 0;

library(tidyverse)

x <- read_csv("~/Downloads/query-hive-2595.csv") %>%
  mutate(date = as.Date(paste(year, month, day, sep = "-"), "%Y-%m-%d")) %>%
  spread(zero_result, searches) %>%
  group_by(date, platform) %>%
  summarize(
    `Total Searches` = True + False,
    `Zero Results Rate` = True / (True + False)
  ) %>%
  ungroup %>%
  gather(metric, value, -c(date, platform))

ggplot(x, aes(x = date, y = value, color = platform)) +
  geom_line() +
  geom_smooth(method = "gam", se = FALSE, formula = y ~ s(x, k = 7), linetype = "dashed") +
  scale_color_brewer(palette = "Set1") +
  facet_wrap(~ metric, scales = "free_y") +
  wmf::theme_facet(14, "Source Sans Pro") +
  labs(
    x = "Date", color = "Platform", y = NULL,
    title = "Searches on Hindi Wikipedia",
    subtitle = "From March 2018 Cirrus search logs"
  )