Query: hackernews_dashboard_ask_and_show_by_hour

with ask_hn as (
  select
    to_char(time::timestamptz,'MM-DD HH24:00') as hour
  from
    hackernews_new
  where
    time::timestamptz > now() - interval '14 day'
    and title ~ '^Ask HN:'
),
show_hn as (
  select
    to_char(time::timestamptz,'MM-DD HH24:00') as hour
  from
    hackernews_new
  where
    time::timestamptz > now() - interval '14 day'
    and title ~ '^Show HN:'
)
select
  hour,
  count(a.*) as "Ask HN",
  count(s.*) as "Show HN"
from
  ask_hn a
left join
  show_hn s
using
  (hour)
group by
  hour
order by
  hour