Facepalm. Thanks--works perfectly now :) Let this thread by stricken from the record.
On Tue, Jun 30, 2015 at 8:40 AM, Madhumitha Viswanathan < [email protected]> wrote: > Forgot to include Jon in the reply. > > 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:39 AM, Madhumitha Viswanathan < > [email protected]> wrote: > >> 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 :) >> > > > > -- > --Madhu :) >
_______________________________________________ Analytics mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/analytics
