title: Postgresql Fill in missing dates using raw sql
body: <p>I have the following tables (simplified below):</p>
  <p><code>Orders:</code></p>
  <pre><code>&lt;id: 1, shipping: 6.0, price: 20.0&gt;
  &lt;id: 2, shipping: 10.0, price: 30.0&gt;
  &lt;id: 3, shipping: 7.0, price: 12.0&gt;
  &lt;id: 4, shipping: 5.0, price: 0.0&gt; #0 dollars because it was updated after return
  </code></pre>
  <p><code>Sales</code>:</p>
  <pre><code>&lt;id: 1, order_id: 1, price:10.0, qty:2, date: &quot;2020-06-01T01:16:15-04:00&quot;&gt;
  &lt;id: 2, order_id: 1, price:9.0, qty: 1, date: &quot;2020-06-01T01:16:15-04:00&quot;&gt;
  &lt;id: 3, order_id: 2, price:15.0, qty:2, date: &quot;2020-06-01T01:23:53-04:00&quot;&gt;
  &lt;id: 4, order_id: 3, price:4.0, qty: 1, date: &quot;2020-06-01T20:28:18-04:00&quot;&gt;
  &lt;id: 5, order_id: 3, price:4.0, qty: 2, date: &quot;2020-06-01T20:31:15-04:00&quot;&gt;
  &lt;id: 6, order_id: 4, price:29.0, qty:1, date: &quot;2020-06-03T20:16:15-04:00&quot;&gt;
  </code></pre>
  <p><code>Refunds</code>:</p>
  <pre><code>&lt;id: 1, order_id: 1, qty:1, amount: 9.0, date: &quot;2020-06-01T01:23:15-04:00&quot;&gt;
  &lt;id: 2, order_id: 4, qty:1, amount: 29.0, date: &quot;2020-06-04T03:34:53-04:00&quot;&gt;
  </code></pre>
  <p>I'm writing raw sql to calculate the <code>shipping</code> (i.e sum(orders.shipping)), <code>total orders</code> (i.e COUNT(DISTINCT orders.id)) and <code>net sales</code>(i.e sales.price * sales.qty - COALESCE(refunds.refund_amount, 0)) grouped by the days. The search is going to take a <code>min_date</code> and <code>max_date</code> in format: <code>YYYY-MM-DDThh24:mi:ss</code> to filter out the sales or refunds that aren't within the date range. The problem I'm having is using <code>generate_series</code> to add in all the days that don't exist in the tables with the values all set to 0. So a sample response if min_date = 2020-06-01T00:00:00 and max_date = 2020-06-05T23:59:59 would be something like:</p>
  <pre><code>&quot;2020-06-01&quot;: {shipping: 6, total_orders: 3, net_sales: 62.0},
  &quot;2020-06-02&quot;: {shipping: 0, total_orders: 0, net_sales: 0}, --&gt; newly added
  &quot;2020-06-03&quot;: {shipping: 5, total_orders: 1, net_sales: 29},
  &quot;2020-06-04&quot;: {shipping: 0, total_orders: 1, net_sales: -29.0},
  &quot;2020-06-05&quot;: {shipping: 0, total_orders: 0, net_sales: 0} --&gt; newly added.
  </code></pre>
  <p>Can anyone help me receive the desired results above. I've seen examples but I can't get it to work with my scenario. Thanks!</p>