WITH nocookie_requests_by_client AS (
  SELECT
    COUNT(*) AS requests,
    hash(ip, user_agent, accept_language, lower(uri_host)) AS id,
    day AS day
  FROM
    wmf.webrequest
  WHERE
    x_analytics_map['nocookies'] IS NOT NULL
    AND agent_type = 'user'
    AND is_pageview = TRUE
    AND webrequest_source = 'text'
    AND year = 2017
    AND month = 9
    AND day >= 6
    AND geocoded_data['country_code'] = 'JP'
    AND access_method = 'desktop'
    AND pageview_info['project'] = 'en.wikipedia'
  GROUP BY
    hash(ip, user_agent, accept_language, lower(uri_host)),
    day
)

SELECT
  SUM(requests) AS requests,
  day AS day
FROM
  nocookie_requests_by_client
WHERE
  requests >= 10
GROUP BY
  day;