Uncited articles

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

Produces a list of articles that do not use citation templates, are expected to have citations, and are not currently tagged as unreferenced

It may be useful to exclude articles with external links but no citation templates.

SQL

WITH RECURSIVE all_citations_templates (page_title, page_id, page_namespace) AS (
  (
    SELECT page_title, page_id, page_namespace
    FROM page
    WHERE page_title = "Citation_templates"
      AND page_namespace = 14
  )
  UNION
  (
    SELECT child.page_title, child.page_id, child.page_namespace
    FROM page AS child
    JOIN categorylinks as childLinks ON child.page_id = childLinks.cl_from
    JOIN all_citations_templates AS parent ON childLinks.cl_to = parent.page_title
    LEFT JOIN page_props ON childLinks.cl_from = pp_page AND pp_propname = "hiddencat"
    WHERE (child.page_namespace = 10
      OR child.page_namespace = 14)
      AND parent.page_namespace = 14
      AND pp_propname IS NULL
  )
), 

WITH RECURSIVE all_set_indices (page_title, page_id, page_namespace) AS (
  (
    SELECT page_title, page_id, page_namespace
    FROM page
    WHERE page_title IN ("Set_indices", "Wikipedia_indexes")
      AND page_namespace = 14
  )
  UNION
  (
    SELECT child.page_title, child.page_id, child.page_namespace
    FROM page AS child
    JOIN categorylinks as childLinks ON child.page_id = childLinks.cl_from
    JOIN all_set_indices AS parent ON childLinks.cl_to = parent.page_title
    LEFT JOIN page_props ON childLinks.cl_from = pp_page AND pp_propname = "hiddencat"
    WHERE (child.page_namespace = 0
      OR child.page_namespace = 14)
      AND parent.page_namespace = 14
      AND pp_propname IS NULL
  )
),

citations_templates AS (
  SELECT DISTINCT(page_id), page_title
  FROM all_citations_templates
  WHERE page_namespace = 10
),

set_indices AS (
  SELECT DISTINCT(page_id), page_title
  FROM all_set_indices
  WHERE page_namespace = 0
)

used_templates AS (SELECT tl_from
  FROM citation_templates
  JOIN templatelinks ON tl_title = page_title
  	AND tl_namespace = 10
    AND tl_from_namespace = 0
),

disambiguation_pages AS (SELECT cl_from
  FROM categorylinks
  WHERE cl_to = "All_article_disambiguation_pages"
),

unreferenced AS (SELECT tl_from
  FROM templatelinks
  WHERE tl_from_namespace = 0
    AND tl_namespace = 10
    AND tl_title = "Unreferenced"
),

excluded_full AS (
  (
    SELECT cl_from as excluded
	FROM disambiguation_pages
  )
  UNION
  (
    SELECT tl_from as excluded
    FROM used_templates
  )
  UNION
  (
    SELECT tl_from as excluded
    FROM unreferenced
  )
),

excluded_partial AS (
  (
    SELECT cl_from as excluded
	FROM disambiguation_pages
  )
  UNION
  (
    SELECT tl_from as excluded
    FROM used_templates
  )
)
	
SELECT page_title, 
  page_len, 
  SUBSTRING(rev_timestamp, 1, 8) as date 
#  CASE WHEN tl_title IS NULL THEN "Untagged" ELSE "Tagged" END as "Tagged as unreferenced"
FROM page
JOIN revision ON page_id = rev_page and rev_parent_id = 0
LEFT JOIN excluded_full ON page_id = excluded
LEFT JOIN externallinks on page_id = el_from
WHERE excluded IS NULL
  AND el_from IS NULL
  AND page_is_redirect = 0
  AND page_namespace = 0

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...