Untitled query #32411

SQL

SET mapred.job.queue.name=nice;
2	
3	WITH counted AS ( -- adapted from https://phabricator.wikimedia.org/T183903#4824489 :
4	         SELECT project,
5	                page_title,
6	                SUM(view_count) as views, -- Some mainspace views are wrongly logged without namespace_id (NULL)
7	                SUM(IF(namespace_id = 0,view_count,0)) AS ns0views,
8	                SUM(IF(access_method != 'desktop', view_count, 0))/SUM(view_count) AS mobile_ratio
9	           FROM wmf.pageview_hourly
10	          WHERE year=2018
11	            AND agent_type = 'user'     
12	            AND page_title != '-'
13	          GROUP BY project, page_title
14	          HAVING ns0views >= 100 -- Some small projects may have very low traffic pages in the top X
15	        ),
16	 
17	        ns0ranked AS (
18	         SELECT project,
19	                page_title,
20	                views,
21	                ns0views,
22	                mobile_ratio,
23	                rank() OVER (PARTITION BY project ORDER BY ns0views DESC) as ranking
24	           FROM counted
25	        )
26	 
27	 SELECT project,
28	        REGEXP_REPLACE(page_title,'_',' ') AS page,
29	        CONCAT('[[', page_title, ']]') AS desktopurl,
30	        views,
31	        ROUND(100 * mobile_ratio, 2) AS mobile_percentage
32	   FROM ns0ranked
33	  WHERE ranking <= 150 -- In case ranking by all views differs a bit (cf. above)
34	  ORDER BY project ASC, views DESC LIMIT 1000000
SQL

Checking query status...
