Confused between inner join and left join- Building report in sql

Question

Tables:

Question: Using these table, produce a table that shows for each day in march 2020 (including weekends) & customer (for every day on or after their onboarding date), how many users were active on the product (viewing a building or creating a note), how many total buildings were viewed and how many notes were created in total. Note the customer should show up even if they were not active on that date.

Desired Output:

My codes in parts:

1. Notes_report

select date(n.created_at) "created_at", c.customer_name, count(n.user_id) "total_notes",count(distinct n.user_id) "active_user"
from customer c left join notes n on c.customer_id=n.customer_id
group by customer_name, date(created_at);  

Output:

2. Views_report

select date(v.created_at) "created_at", c.customer_name, count(v.user_id) "total_views",count(distinct v.user_id) "active_user"
from customer c left join building_views v on c.customer_id=v.customer_id
group by customer_name, date(created_at);

Output:

3) Date and customer_name:

select d.date, c.customer_name
from date_spine d left join customer c on d.date>=c.onboarding_date
where d.date between '2020-03-01' and '2020-03-31';

Output:

Where I am stuck:

• How to combine my first code with the second code and then the resulting table with my 3rd code to come to the desired output. If this approach is not good. Please suggest a better approach. Please do write the code.

Answer 1

Add LEFT JOIN with the subqueries that get the counts you want from each of the other tables.

select d.date, c.customer_name, IFNULL(n.total_notes, 0) total_notes, IFNULL(n.active_user, 0) active_user, IFNULL(v.total_views, 0) total_views
from date_spine d 
left join customer c on d.date>=c.onboarding_date
left join (
    select date(n.created_at) date, customer_id, count(*) total_notes,count(distinct n.user_id) active_user
    from notes
    group by customer_id, date
) AS n ON n.customer_id = c.customer_id AND n.date = d.date
LEFT JOIN (
    select date(v.created_at) date, customer_id, count(*) total_views
    from building_views v
    group by customer_id, date
) AS v ON v.customer_id = c.customer_id AND v.date = d.date
where d.date between '2020-03-01' and '2020-03-31'

The subqueries don't need to join with customer, since only the main query needs to do that to get the name. The subqueries just use the customer ID.