Hmmm, may be because of this -> AND uri_host like '%en.wikipedia.org%'. Access method 'mobile web' implies the domain is en.m.wikipedia.org.
On Tue, Jun 30, 2015 at 8:22 AM, Jon Katz <[email protected]> wrote: > Hi Analytics, > I have a query help question. Trying to run the following to see how many > clicks are generated by ~3000 pages in english wikipedia. I get 0 results > when I run this query, but that seems unlikely as it means nobody clicked > on the mobile web's barack obama page (or any of the other pages I am > looking at) during the hour in question. I am almost sure I have a stupid > 'where' clause in here that is blocking me. Any thoughts? > > Thanks, > J > > > > the table of pages I am looking at is in hive under > jkatz.browse_prototype_page_list4 > > Here is a sample: > > category title url > > Category:20th-century American politicians Al Gore Al_Gore > > Category:20th-century American politicians Barbara Boxer Barbara_Boxer > > Category:20th-century American politicians Ben Cardin Ben_Cardin > > > Here is my query: > SELECT * from jkatz.browse_prototype_page_list4 L > > INNER JOIN ( > > SELECT count(*) AS num_pvs, referer, uri_path > > FROM wmf.webrequest > > WHERE TRUE = TRUE > > AND webrequest_source = 'mobile' > > AND year = 2015 > > AND month = 5 > > AND day = 25 > > AND hour = 1 > > AND agent_type <> 'spider' > > AND is_pageview = TRUE > > AND x_analytics_map['mf-m'] IS NULL > > AND access_method = 'mobile web' > > AND uri_host like '%en.wikipedia.org%' > > GROUP BY > > referer, uri_path > > ) R > > on R.referer = concat('http://en.m.wikipedia.org/wiki/', L.url) > > ORDER BY category > > LIMIT 50; > > > > > _______________________________________________ > Analytics mailing list > [email protected] > https://lists.wikimedia.org/mailman/listinfo/analytics > > -- --Madhu :)
_______________________________________________ Analytics mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/analytics
