Userspace links in articles

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

SELECT pl_from, NS, page_title/*, L_NS*/, L_titles, num_L as '#L', SB, IU, CSD, 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 '(UC)'
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'In_use'
    ) THEN '(IU)'
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'GOCEinuse'
    ) THEN '(CE)'
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'In_creation'
    ) THEN '(IC)'
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Copyvio-revdel'
    ) THEN '(RD1)'
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'History_merge'
    ) THEN '(HM)'
    ELSE ''
  END AS IU,
  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)'
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title IN ('AFC_submission', 'Afc_comment')
    ) THEN '(AfC)'
    WHEN EXISTS (
      SELECT 1
      FROM categorylinks
      WHERE
        cl_from = pl_from
        AND cl_to = 'All_articles_proposed_for_deletion'
    ) THEN '(PROD)'
    ELSE ''
  END AS CSD
  -- Could handle PROD through the category 'All_articles_proposed_for_deletion'
  -- But that template doesn't list the last edit; maybe it should.
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 = '' OR CSD='(AfC)' OR CSD='(PROD)') -- There's no need for me to review every CSD. Or any CSD.
  -- AND (IU != '(UC)' AND IU != '(RD1)') -- Otherwise, there are too many pages listed.
  -- AND (IU = '' OR IU = '(IC)') -- I don't need to see every article that's being copy-edited.
  -- AND (C_PB = '' OR num_L > 2) - Now accomplished by IU - was using 'Pages_using_Under_construction_with_the_placedby_parameter'
  AND (
    (WP = '')
    OR num_L > 1 
  )
  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.
  AND page_title NOT IN (
    'Collapse_(medical)',
    'David_Ferriero',
    'Dirty_Politics',
    'Fermi_paradox',
    'The_hand_of_God'
  ) -- I'll deal with these later; I'm not currently sure of the best way to handle them.
ORDER BY IU
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...
