
<h2><strong>How many widgets did I sell?</strong></h2>



<p>When you’re just starting out, chances are you’ll be asking a very simple question: How many widgets did I sell each day?</p>



<p>Your SQL might look something like this:</p>



<pre class="wp-block-code"><code>select date(created_at) as day, count(1)
from sales
where created_at > now() - interval '30 day'
group by day order by day;</code></pre>



<p>With that SQL, you can expect results like these:</p>



<div class="table-responsive"><figure class="wp-block-table table table-bordered"><table class=""><tbody><tr><td>day</td><td>count</td></tr><tr><td> &nbsp;2014-03-12 </td><td>5</td></tr><tr><td> &nbsp;2014-03-13 </td><td>1</td></tr><tr><td> 2014-03-19 </td><td>6</td></tr><tr><td> &nbsp;2014-03-20 </td><td>4</td></tr><tr><td> &nbsp;2014-03-21 </td><td>1</td></tr><tr><td> 2014-03-24</td><td>1&nbsp; </td></tr><tr><td> &nbsp;2014-04-08 </td><td>2 </td></tr><tr><td> &nbsp;2014-04-09 </td><td>3</td></tr></tbody></table></figure></div>



<p>If you viewed the results in a data visualization tool, you’d get a graph like this:</p>



<figure class="wp-block-image fancybox"><img src="https://cdn.sisense.com/wp-content/uploads/Daily-sales.png" alt="Daily sales chart" class="wp-image-76101"/></figure>



<p>Unfortunately, since you’re just starting out, you’re not selling widgets every day. And notice the zeroes aren’t even showing up! Your investors will not be pleased with such a misleading graph.</p>



<p>If you’re using Postgres, generate_series can help. generate_series produces a table with a given first point, last point, and interval that you specify. In this case, we’ll choose 30 days ago, today, and each day in between. Here’s our revised SQL:</p>



<pre class="wp-block-code"><code>select date(d) as day, count(sales.id) 
from generate_series(
  current_date - interval '30 day', 
  current_date, 
  '1 day'
) d 
left join sales on date(sales.created_at) = d 
group by day order by day;</code></pre>



<p>By building an explicit list of dates in our query, rather than relying on the dates, in the sales table, we made sure every date showed up in our result set.</p>



<div class="table-responsive"><figure class="wp-block-table table table-bordered"><table class=""><tbody><tr><td>day</td><td>count</td></tr><tr><td> &nbsp;2014-03-12 </td><td>5</td></tr><tr><td> &nbsp;2014-03-13 </td><td>1</td></tr><tr><td> &nbsp;2014-03-14 </td><td>0</td></tr><tr><td> &nbsp;2014-03-15 </td><td>0</td></tr><tr><td> &nbsp;2014-03-16</td><td>0</td></tr><tr><td> &nbsp;2014-03-17</td><td>0</td></tr><tr><td> &nbsp;2014-03-18</td><td>0</td></tr><tr><td> &nbsp;2014-03-19</td><td>6</td></tr><tr><td> &nbsp;2014-03-20</td><td>4</td></tr><tr><td> &nbsp;2014-03-21 </td><td>1</td></tr><tr><td> &nbsp;2014-03-22 </td><td>0</td></tr><tr><td> &nbsp;2014-03-23 </td><td>0</td></tr><tr><td> &nbsp;2014-03-24 </td><td>1</td></tr><tr><td> &nbsp;2014-03-25 </td><td>0</td></tr><tr><td> &nbsp;2014-03-26</td><td>0</td></tr><tr><td> &nbsp;2014-03-27 </td><td>0</td></tr><tr><td> &nbsp;2014-03-28 </td><td>0</td></tr><tr><td> &nbsp;2014-03-29 </td><td>0</td></tr><tr><td> &nbsp;2014-03-30 </td><td>0</td></tr><tr><td> &nbsp;2014-03-31 </td><td>0</td></tr><tr><td> &nbsp;2014-03-01 </td><td>0</td></tr><tr><td> &nbsp;2014-03-02 </td><td>0</td></tr><tr><td> &nbsp;2014-03-03 </td><td>0</td></tr><tr><td> &nbsp;2014-03-04</td><td>0</td></tr><tr><td> &nbsp;2014-03-05</td><td>0</td></tr><tr><td> &nbsp;2014-03-06 </td><td>0</td></tr><tr><td> &nbsp;2014-03-07</td><td>0</td></tr><tr><td> &nbsp;2014-03-08</td><td>0</td></tr><tr><td> &nbsp;2014-03-09 </td><td>0</td></tr><tr><td> &nbsp;2014-03-10</td><td>0</td></tr><tr><td> &nbsp;2014-03-11 </td><td>2</td></tr></tbody></table></figure></div>



<p>And here’s the chart:</p>



<figure class="wp-block-image fancybox"><img src="https://cdn.sisense.com/wp-content/uploads/Daily-sales-2.png" alt="Daily sales chart 2" class="wp-image-76108"/></figure>



<p>That’s more like it!</p>



<h2><strong>Caveats and gotchas</strong></h2>



<p>left join: We used generate_series to make sure we got a row for every day in the last 30 days, not just a row for every day in the sales table. So be careful not to use an inner join, which will wipe out the days on the left-hand side that have no matching day on the right-hand side.</p>



<p>count(sales.id): A snippet from our joined table looks like this:</p>



<div class="table-responsive"><figure class="wp-block-table table table-bordered"><table class=""><tbody><tr><td> 2014-03-21 </td><td> 00:00:00 </td><td> 1178 </td><td> satoshi@hashfactory.io </td><td> Dorian </td></tr><tr><td> Nakamoto </td><td> 4096 </td><td> 2014-32-21 </td><td> 22:19:03.36718</td><td></td></tr><tr><td> 2014-03-22 </td><td> 00:00:00 </td><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td><td></td><td></td></tr><tr><td> 2014-02-23 </td><td> 00:00:00  </td><td></td><td></td><td></td></tr><tr><td> </td><td></td><td></td><td></td><td></td></tr><tr><td> 2014-03-24  </td><td>  00:00:00   </td><td> &nbsp;1179 </td><td> <a rel="noreferrer noopener" href="mailto:harry.glaser@sisense.com" target="_blank">h</a>arry.glaser@sisense.com </td><td> Harry&nbsp;  </td></tr><tr><td> Glaser</td><td> 500 </td><td></td><td></td><td></td></tr></tbody></table></figure></div>



<p>As intended, there are blank rows for days where we had no sales. As a result, we can’t just count every row to get number of sales. count(sales.id) will count just the rows with non-null sales IDs, which is what we want.</p>



<h2><strong>Other databases</strong></h2>



<p>Not on Postgres? Here are some generate_series equivalents that are as good or better:</p>



<p><strong>Oracle’s</strong> magic dual table can help:</p>



<pre class="wp-block-code"><code>select sysdate - level from dual connect by level &lt;= 30;</code></pre>



<p><strong>SQL Server’s</strong> recursive with statements are helpful and very impressive in general:</p>



<pre class="wp-block-code"><code>with t as (
  select 0 as d
  union all
  select d + 1
  from t
  where d &lt; 30
) select getdate() - d from t</code></pre>



<p><strong>MySQL</strong>, unfortunately, is trickier. For some clever hacks, start with <a href="https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">this answer on StackOverflow</a>.</p>



<h2><strong>An easier way</strong></h2>



<p>Of course, a visualization tool like Sisense for Cloud Data Teams will continuous-ify the data for you! In our tool of choice, head to a chart’s settings and toggle “Continuous Axis” for the desired effect.</p>


Use generate_series to get continuous results

curve-image-unique-image-unique