Stripping the domain name from a url using SQL

Question

I'm working with Big Query's Hacker News dataset, and was looking at which urls have the most news stories. I'd also like to strip the domain names out, and see which of those have the most news stories. I'm working in R, and am having a bit of trouble getting the follow query to work.

# Select the ten domains that have the most stories
sql_domain <- "SELECT url REPLACE(CASE WHEN REGEXP_CONTAINS(url, '//') 
                        THEN url ELSE CONCAT('http://', url) END, '&', '?') as domain_name,
                      COUNT(domain_name) as story_number
                FROM `bigquery-public-data.hacker_news.full`
                WHERE type = 'story'
                GROUP BY domain_name
                ORDER BY story_number DESC
                LIMIT 10"

I don't need to strip the top-level domain; for example, stackoverflow isn't required, stackoverflow.com is fine. Your help is greatly appreciated!

Maybe you want one of the net functions? cloud.google.com/bigquery/docs/reference/standard-sql/… — Elliott Brossard
– Elliott Brossard, Commented Oct 3, 2018 at 15:08
@ElliotBrossard Very elegant! I'm trying: sql_domain_ag <- "SELECT NET.REG_DOMAIN(url) as domain_name, COUNT(domain_name) as story_number And am now getting "Error: Unrecognized name: domain_name at [2:29] [invalidQuery]" so I must be calling the function improperly, or something. — Luke Steer
– Luke Steer, Commented Oct 3, 2018 at 15:22

Parfait · Accepted Answer · 2018-10-03 16:14:08Z

4

The problem is in your query - you should use as below (for BigQuery Standard SQL)

SELECT 
  NET.REG_DOMAIN(url) AS domain_name,
  COUNT(NET.REG_DOMAIN(url)) AS story_number
FROM `bigquery-public-data.hacker_news.full`
WHERE type = 'story'
GROUP BY 1
ORDER BY story_number DESC
LIMIT 10

this will give you something like below

Row domain_name     story_number     
1   github.com      81784    
2   medium.com      71953    
3   youtube.com     58119    
4   blogspot.com    52925    
5   nytimes.com     48986    
6   techcrunch.com  43924    
7   google.com      26326    
8   wordpress.com   23372    
9   arstechnica.com 23162    
10  wired.com       18480

edited Oct 3, 2018 at 16:14

Parfait

108k19 gold badges103 silver badges138 bronze badges

answered Oct 3, 2018 at 16:08

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stripping the domain name from a url using SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related