DROP TABLE IF EXISTS cross_wiki_navigation;

CREATE TABLE cross_wiki_navigation AS
 SELECT year,
        month,
        day,
        prev,
        curr,
        COUNT(*) AS n

   FROM (SELECT day,
                month,
                year,
                REGEXP_EXTRACT(parse_url(referer,'HOST'), '^([a-z-]{2,}).wikivoyage.org', 1) AS prev,
                normalized_host.project AS curr

           FROM wmf.webrequest
                -- select a relevant timespan to query over
          WHERE year = 2016
                AND month = 6 -- modify the month here!
                AND webrequest_source = 'text'
                AND is_pageview
                -- only consider wikivoyage article requests from users
                AND agent_type = 'user'
                AND normalized_host.project_class = 'wikivoyage'
                -- only consider wikivoyage article referers (this is an approximation)
                AND parse_url(referer,'HOST') LIKE '%wikivoyage.org'
                -- exclude .m.wikivoyage.org and .zero.wikivoyage.org
                AND parse_url(referer,'HOST') NOT LIKE '%.%.wikivoyage.org'
                AND parse_url(referer,'PATH') LIKE '/wiki/%'

        ) log_with_parsed_referer
  WHERE prev != curr
  GROUP BY
        year,
        month,
        day,
        prev,
        curr
;