Userspace links in articles

SQL

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'
    ) 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
  (
    (C = '' AND RD1 = '' AND WP = '' AND CSD = '')
    OR num_L > 1 
  )
  AND (C_PB = '' OR num_L > 2)
SQL

Checking query status...
