Query: hackernews_sources_domains

with domains as (
  select
    url,
    substring(url from 'http[s]*://([^/$]+)') as domain
  from
    hackernews_new where $1 = 'New' and url != '<null>'
  union
  select
    url,
    substring(url from 'http[s]*://([^/$]+)') as domain
  from
    hackernews_top where $1 = 'Top' and url != '<null>'
  union
  select
    url,
    substring(url from 'http[s]*://([^/$]+)') as domain
  from
    hackernews_best where $1 = 'Best' and url != '<null>'
),
avg_and_max as (
  select
    substring(url from 'http[s]*://([^/$]+)') as domain,
    avg(score) as avg_score,
    max(score) as max_score,
    avg(descendants) as avg_comments,
    max(descendants) as max_comments
  from
    hackernews_new
  --where
    --descendants is not null
  group by
    substring(url from 'http[s]*://([^/$]+)')
),
counted as (
  select
    domain,
    count(*)
  from
    domains
  group by
    domain
  order by
    count desc
)
select
  a.domain as "Domain",
  c.count as "Count",
  a.max_score as "Max Score",
  round(a.avg_score, 1) as "Avg Score",
  a.max_comments as "Max Comments",
  round(a.avg_comments, 1) as "Avg Comments"
from
  avg_and_max a
join
  counted c
using
  (domain)
order by
  count desc