Top URLs used by Wikiproject

This is for a WikiProject that maintains their links of relevant pages as pagelinks on a single page. For a wiki like English that uses the PageAssessments extension, you could replace the `WITH wikiproject_pages...` subquery with something like the following:

```
WITH wikiproject_pages AS (
  SELECT DISTINCT
    pa_page_id AS page_id,
FROM page_assessments
INNER JOIN page_assessments_projects
  ON (pap_project_id = pa_project_id
      AND pap_project_title = 'Climate change')
INNER JOIN page
  ON (pa_page_id = page_id
      AND page_namespace = 0)
)
```

SQL

WITH wikiproject_pages AS (
  SELECT
    p.page_id
  FROM pagelinks pl
  INNER JOIN linktarget t
    ON (pl.pl_target_id = t.lt_id)
  INNER JOIN page p
    ON (t.lt_title = p.page_title)
  WHERE
    pl_from = 9797509  # https://es.wikipedia.org/wiki/Wikiproyecto:Cambio_clim%C3%A1tico/seguimiento
    AND p.page_namespace = 0
)
SELECT
  CONCAT(REGEXP_REPLACE(el_to_domain_index,
                             '^(.*?://)(?:([^.]+)\\.)([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)$',
                             '\\1\\9\\8\\7\\6\\5\\4\\3\\2'),
              el_to_path) AS url,
  COUNT(DISTINCT(page_id)) AS num_pages_linking_to_this_url
FROM externallinks el
INNER JOIN wikiproject_pages p
  ON (el.el_from = p.page_id)
GROUP BY
  CONCAT(REGEXP_REPLACE(el_to_domain_index,
                             '^(.*?://)(?:([^.]+)\\.)([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)$',
                             '\\1\\9\\8\\7\\6\\5\\4\\3\\2'),
              el_to_path)
ORDER BY
  num_pages_linking_to_this_url DESC
LIMIT 100

SQL

Checking query status...