title: How to fill missing dates in BigQuery by partition (no backfill)
body: <p>Related to <a href="https://stackoverflow.com/questions/66983100/how-to-fill-missing-dates-in-bigquery">How to fill missing dates in BigQuery?</a>, but without back filling/forward filling:</p>
  <p>Suppose I have the following sales data:</p>
  <pre><code>order_date      product         store           sales
  _______________________________________________________
  2021-05-01      1               1               10
  2021-05-04      1               1               5
  
  2021-04-25      1               2               3
  2021-04-30      1               2               9
  2021-05-10      1               2               1
  
  2021-05-02      2               1               3
  2021-05-04      2               1               3
  </code></pre>
  <p>and my desired output is to fill empty dates w/ 0, but partitioned on <code>product</code> and <code>store</code> (and not to back fill to a global <code>MIN(DATE)</code> or forward fill to a global <code>MAX(DATE)</code>:</p>
  <pre><code>order_date      product         store           sales
  _______________________________________________________
  2021-05-01      1               1               10
  2021-05-02      1               1               0
  2021-05-03      1               1               0
  2021-05-04      1               1               5
  
  2021-04-25      1               2               3
  2021-04-26      1               2               0
  ...             ...             ...             ... (all 0s)
  2021-04-29      1               2               0
  2021-04-30      1               2               9
  ...             ...             ...             ... (all 0s)
  2021-05-10      1               2               1
  
  2021-05-02      2               1               3
  2021-05-03      2               1               0
  2021-05-04      2               1               3
  </code></pre>
  <p>How would I accomplish this (specifically in BigQuery)?</p>
  <p>My initial thought is that I may need to create a table like</p>
  <pre><code>SELECT 
      product, store,
      MIN(order_date) min_date,
      MAX(order_date) max_date
  FROM mydata
  GROUP BY product, store
  </code></pre>
  <p>but I'm not sure how I'd use that to partition the original <code>mydata</code> table and fill dates between with <code>IFNULL(sales,0) sales</code></p>
  <p>Thanks in advance!</p>