Wikipediapages without WikiData Items, ordered by Pageviews

Fork of Wikipediapages without WikiData Items, ordered by Pageviews by Ranjithraj

This query is marked as a draft This query has been published by Kasyap.

SQL

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;

SQL

Checking query status...