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

SQL

AخA
 
SELECT pl_from, NS, page_title/*, L_NS*/, L_titles, num_L, SB, IU, WP, SR
FROM (
SELECT
  pl_from,
  -- pl_from_namespace does not appear to be consistently reliable;
  -- it might be better to select from the page table and join the pagelinks table to it.
  CASE
    -- This fails on pages missing from the page table (presumably because they were deleted).
    WHEN pl_from_namespace != page_namespace THEN CONCAT(pl_from_namespace, ' vs. ', page_namespace)
    ELSE pl_from_namespace
  END AS NS,
  page_title,
  pl_namespace AS L_NS,
  GROUP_CONCAT(pl_title SEPARATOR ' ') AS L_titles,
  COUNT(pl_title) AS num_L,
  CASE
    WHEN MAX(CASE WHEN pl_title LIKE '%/sandbox' THEN 1 END) = 1 THEN '(SB)'
    ELSE ''
  END AS SB,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Under_construction'
    ) THEN '(C)'
    ELSE ''
  END AS C,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM categorylinks
      WHERE
        cl_from = pl_from
        AND cl_to = 'Pages_using_Under_construction_with_the_placedby_parameter'
    ) THEN '(PB)'
    ELSE ''
  END AS C_PB,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND (tl_title = 'In_use' OR tl_title = 'GOCEinuse')
    ) THEN '(IU)'
    ELSE ''
  END AS IU,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Copyvio-revdel'
    ) THEN '(RD1)'
    ELSE ''
  END AS RD1,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Wikipedia_person_user_link'
    )
    OR page_title LIKE '%Wiki%'
    OR page_title = 'The_Signpost' THEN '(WP)'
    ELSE ''
  END AS WP,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Self-reference_link'
    ) THEN '(SR)'
    ELSE ''
  END AS SR,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM categorylinks
      WHERE
        cl_from = pl_from
        AND cl_to = 'Candidates_for_speedy_deletion'
    ) THEN '(CSD)'
    ELSE ''
  END AS CSD
FROM pagelinks
LEFT JOIN page ON page_id = pl_from
WHERE
  pl_from_namespace = 0
  AND pl_namespace = 2
  -- In the future: AND pl_namespace != 0
GROUP BY pl_from
ORDER BY SB, page_title
) AS t1
WHERE
  CSD = '' -- There's no need for me to review every CSD. Or any CSD.
  AND (
    (C = '' AND RD1 = '' AND WP = '')
    OR num_L > 1 
  )
  AND (C_PB = '' OR num_L > 2)
  AND page_title != 'Main_Page' -- Is a special mainspace page: it shouldn't be treated like others and isn't useful to report on.
  AND WP != '(WP)' -- I will handle these separately; they usually require more attention to all types of cross-namespace links.
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
All SQL code is licensed under CC0 License.

Checking query status...