SELECT        
   year, month, day,
    geocoded_data['country_code'] AS country,
    access_method,
    agent_type,
    referer_class,
    user_agent, 
    referer, 
    COUNT(1) AS views
FROM wmf.webrequest
WHERE  
    webrequest_source = "text"
    AND is_pageview
    AND year = 2023
    AND month = 03
    AND day IN (26,19) 
    AND referer like '%chat.openai.com/' --chats from ChatGPT
GROUP BY
    year, month, day,
    geocoded_data['country_code'],
    access_method,
    agent_type,
    referer_class,
    user_agent,
    referer

 SELECT            
    year, month, day,
    access_method,
    agent_type,
    referer_class,
    user_agent, 
    referer, 
    COUNT(1) AS views
FROM wmf.pageview_actor
WHERE  
    year = 2023
    AND month = 03
    AND day IN (26,19) 
    AND referer like '%chat.openai.com%' --chats from ChatGPT
GROUP BY
    year, month, day,
    access_method,
    agent_type,
    referer_class,
    user_agent,
    referer

AND referer like '%edgeservices.bing.com%' --pageviews referred from Bing Chat

AND referer like '%bard.google.com%' --pageviews referred from Google's AI chatbot Bard

"""
WITH base AS (
select  http.client_ip, 
        search_id,
        http.request_headers,
        params,
        user_agent_map,
        hits, 
        elasticsearch_requests
from event.mediawiki_cirrussearch_request 
where year        == 2023                      AND  
      month       == {month}                        AND
      day             == {day}                        AND
      database    =='enwiki'                   AND  
      source      == 'api'                     AND 
      params IS NOT NULL                       AND  
      hits IS NOT NULL                         AND 
      http.method == 'GET'                     AND
      element_at(params, 'action') IS NOT NULL AND
      element_at(params, 'action') =='query'  
)
      
SELECT * 
FROM base
WHERE 
   request_headers['user-agent']  ='USER_AGENT'    AND --https://gitlab.wikimedia.org/repos/machine-learning/chatgpt-plugin/-/blob/dev/app/services/wikipedia_search_service.py
   params["format"]                  = "json"      AND --https://gitlab.wikimedia.org/repos/machine-learning/chatgpt-plugin/-/blob/dev/app/services/wikipedia_search_service.py
   params["action"]                  = "query"      --https://gitlab.wikimedia.org/repos/machine-learning/chatgpt-plugin/-/blob/dev/app/services/wikipedia_search_service.py
"""

"""
WITH base AS (
select http.client_ip, 
        http.request_headers, 
        params
from event.mediawiki_cirrussearch_request 
where year        == 2023         AND  
      month       == 7            AND  
      day         == 19           AND  
      hour        == 9            AND 
      database    =='enwiki'      AND  
      source      == 'api'        AND 
      params IS NOT NULL          AND  
      hits IS NOT NULL            AND 
      http.method == 'GET'         
)
    
SELECT * 
FROM base
WHERE client_ip   LIKE "23.102.140%"
                        
"""

"""
WITH base AS (
select  http.client_ip, 
        search_id,
        http.request_headers,
        params,
        user_agent_map,
        hits, 
        elasticsearch_requests
from event.mediawiki_cirrussearch_request 
where year        == 2023                      AND  
      month       == 7                         AND
      day         == 25                         AND
      --database    =='enwiki'                   AND  
      source      == 'api'                     AND 
      params IS NOT NULL                       AND  
      hits IS NOT NULL                         AND 
      http.method == 'GET'                     AND
      element_at(params, 'action') IS NOT NULL AND
      element_at(params, 'action') =='query'   AND
      http.request_headers['user-agent'] ='wikipedia-chagpt-plugin bot' 
)
      
SELECT * 
FROM base
WHERE 
   --request_headers['user-agent']  ='wikipedia-chagpt-plugin bot'    AND --https://gitlab.wikimedia.org/repos/machine-learning/chatgpt-plugin/-/blob/dev/app/services/wikipedia_search_service.py
   params["format"]                  = "json"      AND 
   params["action"]                  = "query"     
   
"""

SELECT
  DATE_TRUNC('day', CAST(FROM_ISO8601_TIMESTAMP(meta.dt) as TIMESTAMP)) AS __timestamp,
  COUNT(1) AS n_searches
FROM event.mediawiki_cirrussearch_request 
WHERE year = 2023 AND month >= 7
  AND http.request_headers['user-agent'] = 'wikipedia-chagpt-plugin bot'
GROUP BY 1