Select query to show missing values per day as 0

I have one table with product id’s and another with daily data for these products. When there is no data I want to return 0 instead of no value at all. I tried using a left join but this doesn’t give the resultset I want since the table I can join on has product id’s only, not per day.

I guess I have to generate a table first with all products for all dates and then join my data on that. Not sure how to construct this though.

Example of the products table:

id - product
1 - test1
2 - test2
3 - test3

example of the daily data:

product - date - value
test1 - 2020-01-01 - 10
test2 - 2020-01-01 - 8
test3 - 2020-01-01 - 9
test1 - 2020-01-02 - 9
test3 - 2020-01-02 - 10
test2 - 2020-01-03 - 6
test3 - 2020-01-03 - 5

Result I’m looking for:

product - date - value
test1 - 2020-01-01 - 10
test2 - 2020-01-01 - 8
test3 - 2020-01-01 - 9
test1 - 2020-01-02 - 9
test2 - 2020-01-02 - 0
test3 - 2020-01-02 - 10
test1 - 2020-01-03 - 0
test2 - 2020-01-03 - 6
test3 - 2020-01-03 - 5

Answer

You can cross join to get the rows and then left join to bring in the data you want:

select p.id, c.date, coalesce(d.value, 0) as value
from products p cross join
     (select distinct date from daily) c left join
     daily d
     on d.product = p.id and d.date = c.date;

If there are dates that are not in the table, you can generate the dates using generate_series():

select p.id, c.date, coalesce(d.value, 0) as value
from products p cross join
     (select generate_series(min(d.date), max(d.date), interval '1 day') as date
      from daily
     ) c left join
     daily d
     on d.product = p.id and d.date = c.date;