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