Google Sheets Query - fill zeros for dates when no data was recorded

Question

How do I change my query in this google sheet so that it generates a table that has a column for every week, even if there is no data for that week? (display 0 in the values fields)

The problem I'm running into is that if there is a week or date with no data, it's not listed. I would like to generate a row/column with 0 for dates without data.

This is how the query is currently written:

=QUERY(A3:D9,"Select A, sum(C) group by A pivot D")

Here's the sheet (hyperlinked so you can see it):

Answer 1

The basic problem you need to solve is to know which data pieces are missing. Do you need the entries for every single day in a given date range? Only weekdays? Only weekdays, except public holidays? etc.

Once you know that, you can insert the missing data in the query itself, by concatenating the source table with literal data as below (where I'm manually adding a combination of Red with Nov 5), or with another query/resultset of another formula that gives you the missing data:

=QUERY({A3:D9; {"Red", date(2018,11,5), 0, weeknum(date(2018,11,5))}},
       "Select Col1, sum(Col3) group by Col1 pivot Col4")