ADD JAR hdfs:///wmf/refinery/current/artifacts/refinery-hive.jar;
CREATE TEMPORARY FUNCTION search_classify AS 'org.wikimedia.analytics.refinery.hive.GetSearchRequestTypeUDF';
USE wmf;
WITH users AS (
SELECT
  CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) AS date,
  client_ip, user_agent,
  SUM(IF(search_classify(uri_path, uri_query) = 'prefix', 1, 0)) AS n_prefix,
  SUM(IF(is_pageview AND referer_class = 'internal' AND page_id IS NULL AND uri_path = '/w/index.php' 
         AND (LENGTH(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'search')) > 0
         OR LENGTH(PARSE_URL(CONCAT('http://', uri_host, uri_path, uri_query), 'QUERY', 'searchToken')) > 0), 
         1, 0)) AS n_fulltext
FROM webrequest
WHERE year = 2017 AND month = 9 AND day >= 1 AND day <= 15
  AND webrequest_source = 'text'
  AND access_method = 'mobile web'
  AND agent_type = 'user'
  AND http_status = '200'
GROUP BY CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')), client_ip, user_agent
-- Filter users(client_ip + user_agent) with prefix search
HAVING array_contains(collect_set(search_classify(uri_path, uri_query)), 'prefix')
)
SELECT 
  date,
  COUNT(*) AS n_users,
  SUM(IF(n_fulltext > 0 , 1, 0)) AS user_has_fulltext,
  SUM(n_prefix) AS n_prefix,
  SUM(n_fulltext) AS n_fulltext
FROM users
GROUP BY date;