Compare counts of two tables joined on week - SQL

Question

I'm a newbie in SQL. I have two tables. I want to count the number of occurrences of one thing each week in the first, and of another thing each week in the second, and then compare them. I already have the codes for counting in two separate graphs bu can't seem to be able to join them.

My first count :

select 
  date_part('week',Table2.date at time zone 'utc' at time zone 'Europe/Paris') as week, 
  count(Table2.issue_solved) as count2
from Table2
where date is not null
group by week
order by week asc

My second count

select 
  date_part('week',Table1.activity_date at time zone 'utc' at time zone 'Europe/Paris') as week, 
  count(distinct Table1.activity_id) as count1
from Table1
left join X
  on Y1 = Y2
left join W
  on A1 = A2
  and B1 = B2

where activity_dimensions.type in ('Training')
  and acquisition_opportunity_dimensions.product_family = 'EHR'
  and activity_dimensions.country = 'fr'
  and activity_date::date >= date_trunc('[aggregation]', [daterange_start])
  and activity_date::date <= [daterange_end]
  and activity_date::date <= current_date

group by week
order by count_training_meetings desc

I tried to join the first code into the second with a join on week, but I can't seem to make this work.

Any idea?

Answer 1

Not sure if periscope allows full join, but if you have some weeks in your first data set (query) which don't appear in the second one, and vice versa, you should use this operator in order to retrieve everything.

coalesce is intend to get the first value it recognices as not null.

In standard sql, it should be something like this

select
  coalesce(q1.week, q2.week) as week,
  count1,
  count2
from 
  (
    select 
      date_part('week',Table2.date at time zone 'utc' at time zone 'Europe/Paris') as week, 
      count(Table2.issue_solved) as count2
    from Table2
    where date is not null
    group by week
  ) q1
  full join
  (
    select 
      date_part('week',Table1.activity_date at time zone 'utc' at time zone 'Europe/Paris') as week, 
      count(distinct Table1.activity_id) as count1
    from Table1
    left join X
      on Y1 = Y2
    left join W
      on A1 = A2
      and B1 = B2
    where activity_dimensions.type in ('Training')
      and acquisition_opportunity_dimensions.product_family = 'EHR'
      and activity_dimensions.country = 'fr'
      and activity_date::date >= date_trunc('[aggregation]', [daterange_start])
      and activity_date::date <= [daterange_end]
      and activity_date::date <= current_date
    group by week
  ) q2
    on q1.week = q2.week

As I told you in previous comments, maybe it could be wrong to mix weeks from different years if they are present on your data, but this is just a suggestion