SELECT DISTINCT

  p.page_title,

  p.page_len,

  cl.cl_to,

  COALESCE(views.view_count, 0) as page_views

FROM page p

JOIN categorylinks cl ON p.page_id = cl.cl_from

LEFT JOIN page_props pp ON p.page_id = pp.pp_page

  AND pp.pp_propname = 'wikibase_item'

LEFT JOIN (

  SELECT page_title, SUM(view_count) as view_count

  FROM analytics.pageview_complete

  WHERE wiki_db = 'tewiki'

  AND timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)

  AND agent_type = 'user'

  GROUP BY page_title

) views ON p.page_title = views.page_title

WHERE p.page_namespace = 0

  AND p.page_is_redirect = 0

  AND pp.pp_value IS NULL

  AND cl.cl_to LIKE '%category'

ORDER BY page_views DESC, p.page_title;