WITH articles AS (

  SELECT DISTINCT(Article.page_title), Article.page_id, rev_timestamp, rev_len as "InitialSize", Article.page_len as "CurrentSize"

FROM revision_userindex

JOIN page AS Article ON Article.page_id = rev_page AND Article.page_namespace = 0 

JOIN page AS Talk ON Article.page_title = Talk.page_title AND Talk.page_namespace = 1

JOIN categorylinks AS ArticleCat ON ArticleCat.cl_from = Article.page_id

JOIN categorylinks AS TalkCat ON TalkCat.cl_from = Talk.page_id

WHERE rev_parent_id = 0

    AND Article.page_is_redirect = 0

    AND rev_actor = 482

    AND (ArticleCat.cl_to LIKE '%1896_Summer_Olympics'

        OR ArticleCat.cl_to LIKE '%1900_Summer_Olympics'

        OR ArticleCat.cl_to LIKE '%1904_Summer_Olympics'

        OR ArticleCat.cl_to LIKE '%1908_Summer_Olympics'

        OR ArticleCat.cl_to LIKE '%1912_Summer_Olympics')

    AND (TalkCat.cl_to LIKE '%iography%')

    AND Article.page_len < 2500

),

​

articlessansmedalists AS (

  SELECT page_title, page_id, rev_timestamp, InitialSize, CurrentSize

  FROM articles

  LEFT JOIN categorylinks as ArticleCatExc ON ArticleCatExc.cl_from = page_id AND ArticleCatExc.cl_to IN (

      'Medalists at the 1896 Summer Olympics',

      'Medalists at the 1900 Summer Olympics',

      'Medalists at the 1904 Summer Olympics',

      'Medalists at the 1908 Summer Olympics',

      'Medalists at the 1912 Summer Olympics')

  WHERE ArticleCatExc.cl_to IS NULL

),

​

reference AS (

  SELECT page_title, el_index, el_to, LEFT(el_index,22) as el_index_22

  FROM articlessansmedalists

  JOIN externallinks ON el_from = page_id

  WHERE LEFT(el_index,17) NOT IN ("http://com.google", "https://org.jstor", "https://com.googl", "https://org.wikid", "https://org.archi")

)

​

SELECT el_index_22, count(*) as occurrences

FROM reference

GROUP BY el_index_22

ORDER BY occurrences DESC