WITH raw AS (
  SELECT content AS html FROM read_text('page.html')
),
anchors AS (
  SELECT unnest(regexp_extract_all(html, '<a [^>]*href="(https?://[^"]+)"[^>]*>(.*?)</a>', ['1','2'])) AS pair
  FROM raw
),
split AS (
  SELECT pair['1'] AS url, pair['2'] AS name FROM anchors
),
clean AS (
  SELECT
    trim(regexp_replace(url, '&amp;', '&', 'g')) AS url,
    trim(regexp_replace(regexp_replace(name, '<[^>]*>', '', 'g'), '&amp;', '&', 'g')) AS name
  FROM split
),
companies AS (
  SELECT * FROM clean
  WHERE url NOT LIKE '%sandiego.edu%'
    AND url NOT LIKE '%facebook.com%'
    AND url NOT LIKE '%twitter.com%'
    AND name NOT SIMILAR TO '(Skip to content|Home|Request Info|Back to Blog.*|Computer Science|Engineering|Technology|Show Me.*|Apply Now|100 Most Influential.*|protect sensitive information|."Best Cybersecurity Company".|"Partner of the Year"|RELATED.*)'
    AND length(name) BETWEEN 2 AND 60
    AND url NOT LIKE '%time.com%' AND url NOT LIKE '%liaisoncas.com%' AND url NOT LIKE '%nordvpn.com%' AND url NOT LIKE '%excellence-awards.com%' AND url NOT LIKE '%careers.trellix.com%' AND url NOT LIKE '%cyderes.com/news-feed%'
)
SELECT name, url, regexp_replace(host, '^www\.', '') AS domain
FROM (
  SELECT name, url2 AS url,
         regexp_extract(url2, 'https?://([^/]+)', 1) AS host
  FROM (
    SELECT name,
           CASE WHEN url LIKE '%herjavecgroup.com%' THEN 'https://www.cyderes.com/' ELSE url END AS url2
    FROM companies
  ) a
) t
QUALIFY row_number() OVER (PARTITION BY regexp_replace(regexp_extract(url, 'https?://([^/]+)', 1), '^www\.', '') ORDER BY length(url)) = 1
ORDER BY name;