USE hrwiki_p;

SELECT page_title, el_to, page_touched, REGEXP_SUBSTR(el_to, '//\\K([^/]*)') AS portal

  FROM externallinks

  JOIN page ON page_id=el_from

  WHERE page_namespace=0 AND el_to like '%.hgk.hr%'

  ORDER BY el_id DESC

  limit 25;

SELECT count(el_id) as broj, REGEXP_SUBSTR(el_to, '//(:?www\\.)?\\K([^/]*)') AS portal

  FROM externallinks

  JOIN page ON page_id=el_from

  WHERE page_namespace=0 AND REGEXP_SUBSTR(el_to, '//\\K([^/]*)') like '%.hr'

  GROUP BY portal

  ORDER BY broj DESC

  limit 200;