Untitled query #40941

SQL

SET mapred.job.queue.name=nice;
	
	WITH counted AS ( -- adapted from https://phabricator.wikimedia.org/T183903#4824489 :
	         SELECT project,
	                page_title,
	                SUM(view_count) as views, -- Some mainspace views are wrongly logged without namespace_id (NULL)
	                SUM(IF(namespace_id = 0,view_count,0)) AS ns0views,
	                SUM(IF(access_method != 'desktop', view_count, 0))/SUM(view_count) AS mobile_ratio
	           FROM wmf.pageview_hourly
	          WHERE year=2018
	            AND agent_type = 'user'     
	            AND page_title != '-'
	          GROUP BY project, page_title
	          HAVING ns0views >= 100 -- Some small projects may have very low traffic pages in the top X
	        ),
	 
	        ns0ranked AS (
	         SELECT project,
	                page_title,
	                views,
	                ns0views,
	                mobile_ratio,
	                rank() OVER (PARTITION BY project ORDER BY ns0views DESC) as ranking
	           FROM counted
	        )
	 
	 SELECT project,
	        REGEXP_REPLACE(page_title,'_',' ') AS page,
	        CONCAT('https://', project, '.org/wiki/', page_title) AS desktopurl,
	        views,
	        ROUND(100 * mobile_ratio, 2) AS mobile_percentage
	   FROM ns0ranked
	  WHERE ranking <= 150 -- In case ranking by all views differs a bit (cf. above)
	  ORDER BY project ASC, views DESC LIMIT 1000000
SQL

Checking query status...
