title: How to do Week over Week Increase in SQL
body: <p>ads Table:<br />
  -one row per ad per day</p>
  <pre><code>date    |   ad_id  | account_id | spend  
  2018-05-01   123     1101        100  
  2018-05-02   123     1101        125  
  2018-05-03   124     1101        150  
  2018-05-04   124     1101        150    
  2018-05-04   125     1105        150  
  2018-05-04   126     1105        150  
  2018-05-04   123     1101        150  
  2018-01-01   123     1101        150              
  </code></pre>
  <p>I am trying to write a query to find out: on each day, the total spend and the week-over-week change since 1st January.</p>
  <p>So, week over week should show 8th day's total spending - 1st days total spending. I can achieve that lag window function but what I am not sure what to do if the first day is not mentioned in the date column. Let's say there's no spending on the first day of may then the answer would go wrong if I had used lag function. Is there a way that I could write a query that would look for the total spending through dates rather than lag function? and if on the first day I have no spending, I could get 1200-0=1200 is the WOW change. Also, I can't create a dates table that I can join the ads table on.</p>
  <p>I have written this much so far:</p>
  <pre><code>select dates, sum(spend) &quot;total_spend_each_day&quot;,
  from fb_ads as f
  where dates&gt;= '2018-01-01'
  group by dates
  order by 1;
  </code></pre>
  <p>Desired Output:</p>
  <pre><code>date     |  total_spend_each_day |  Week_over_week_change
  2018-05-01       500                    Null
  2018-05-02       600                    Null
  2018-05-03       700                    Null
  2018-05-04       800                    Null
  2018-05-05       900                    Null
  2018-05-06       1000                   Null
  2018-01-07       1100                   Null
  2018-01-08       1200                   700
  </code></pre>