SELECT
  DATE(LEFT(`timestamp`, 8)) AS `date`,
  event_source AS source,
  event_searchSessionId AS session_id,
  event_uniqueId AS event_id,
  COUNT(*) AS events
FROM `TestSearchSatisfaction2_15922352`
WHERE `timestamp` BETWEEN '20161105000000' AND '20161110000000'
  AND event_subTest IS NULL
  AND event_action = 'searchResultPage'
GROUP BY `date`, source, session_id, event_id
ORDER BY events DESC, `date`, source, session_id, event_id
LIMIT 20;

SELECT
  `date`, source,
  SUM(IF(events > 1, 1, 0)) AS `SERP events duplicated at least once`,
  COUNT(DISTINCT(session_id)) AS `Unique session IDs`,
  SUM(events) AS `Total number of SERP events`,
  COUNT(DISTINCT(event_id)) AS `Number of unique SERP events`
FROM (
  SELECT
    DATE(LEFT(`timestamp`, 8)) AS `date`,
    event_source AS source,
    event_searchSessionId AS session_id,
    event_uniqueId AS event_id,
    COUNT(*) AS events
  FROM `TestSearchSatisfaction2_15922352`
  WHERE `timestamp` BETWEEN '20161105000000' AND '20161110000000'
    AND event_subTest IS NULL
    AND event_action = 'searchResultPage'
  GROUP BY `date`, source, session_id, event_id
) AS refined_events
GROUP BY `date`, source
ORDER BY source ASC, `date` ASC
LIMIT 1000;

SELECT
  wiki_v2, source, `date`,
  SUM(IF(events > 1, 1, 0)) AS `SERP events duplicated at least once`,
  COUNT(DISTINCT(session_id)) AS `Unique session IDs`,
  SUM(events) AS `Total number of SERP events`,
  COUNT(DISTINCT(event_id)) AS `Number of unique SERP events`
FROM (
  SELECT
    DATE(LEFT(`timestamp`, 8)) AS `date`,
    CASE WHEN wiki IN('enwiki', 'frwiki') THEN wiki ELSE 'other' END AS wiki_v2,
    event_source AS source,
    event_searchSessionId AS session_id,
    event_uniqueId AS event_id,
    COUNT(*) AS events
  FROM `TestSearchSatisfaction2_15922352`
  WHERE `timestamp` BETWEEN '20161105000000' AND '20161110000000'
    AND event_subTest IS NULL
    AND event_action = 'searchResultPage'
  GROUP BY `date`, wiki_v2, source, session_id, event_id
) AS refined_events
GROUP BY `date`, source, wiki_v2
ORDER BY wiki_v2 ASC, source ASC, `date` ASC
LIMIT 1000;

SELECT
  wiki, `date`,
  MAX(total_serps) AS `Largest number of (total) SERPs per user`,
  MAX(unique_serps) AS `Largest number of (unique) SERPs per user`
FROM (
  SELECT
    wiki, `date`, session_id,
    SUM(events) AS total_serps,
    COUNT(DISTINCT(event_id)) AS unique_serps
  FROM (
    SELECT
      DATE(LEFT(`timestamp`, 8)) AS `date`,
      wiki,
      event_searchSessionId AS session_id,
      event_uniqueId AS event_id,
      COUNT(*) AS events
    FROM `TestSearchSatisfaction2_15922352`
    WHERE `timestamp` BETWEEN '20161105000000' AND '20161110000000'
      AND event_subTest IS NULL
      AND event_action = 'searchResultPage'
      AND wiki IN('enwiki', 'frwiki')
      AND event_source = 'fulltext'
    GROUP BY `date`, wiki, session_id, event_id
  ) AS refined_events
  GROUP BY `date`, wiki, session_id
) AS per_user
GROUP BY wiki, `date`
ORDER BY wiki ASC, `date` ASC
LIMIT 100;

SELECT
  wiki, `date`, session_id, user_agent,
  MAX(search_token) AS a_search_token,
  SUM(events) AS total_serps,
  COUNT(DISTINCT(event_id)) AS unique_serps
FROM (
  SELECT
    DATE(LEFT(`timestamp`, 8)) AS `date`,
    wiki,
    event_searchSessionId AS session_id,
    event_uniqueId AS event_id,
    userAgent AS user_agent,
    event_searchToken AS search_token,
    COUNT(*) AS events
  FROM `TestSearchSatisfaction2_15922352`
  WHERE `timestamp` BETWEEN '20161105000000' AND '20161110000000'
    AND event_subTest IS NULL
    AND event_action = 'searchResultPage'
    AND wiki IN('enwiki', 'frwiki')
    AND event_source = 'fulltext'
  GROUP BY `date`, wiki, session_id, event_id
) AS refined_events
GROUP BY `date`, wiki, session_id, user_agent
HAVING total_serps > 10000
ORDER BY total_serps, `date`, wiki
LIMIT 10;

SELECT *
FROM CirrusSearchRequestSet
WHERE
  year = 2016 AND month = 11 AND day IN(6, 8)
  AND id IN('zzfdexh3lg0qfsddn5ghj7zy', 'zysoeo38hflh4bujconb7p2w');

SELECT
  CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) AS date,
  COUNT(DISTINCT(wikiid)) AS wikis_searched,
  COUNT(1) AS searches
FROM CirrusSearchRequestSet
WHERE
  year = 2016 AND month = 11 AND day BETWEEN 1 AND 14
  AND source = 'web'
  AND ip = '${HARVARDUNIV}'
  AND requests[size(requests)-1].querytype = 'full_text'
GROUP BY CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0'))
ORDER BY date
LIMIT 100;