title: Calculate month-over-month or year-over-year differences in BigQuery
body: <p>I'm wanting to calculate time-based comparison metrics in BigQuery and am unclear which of the functions JOIN, LAG, WINDOW is most effective for calculating these common metrics. </p>
  
  <p>I've tried looking at other code written for POSTGRES or other SQL flavors and they aren't working correctly in BQ. The BQ documentation for LAG doesn't show any calculations in the creation of a new field. </p>
  
  <p>Here's an example of what I'm looking to calculate</p>
  
  <pre><code>Date Sales
  2019-04-01 100
  2019-03-01 80
  2019-02-01 60
  ....
  2018-04-01 70
  2018-03-01 60
  2018-02-01 50
  </code></pre>
  
  <p>Performing a month over month comparison should create a third column 'pct_growth'</p>
  
  <pre><code>Date Sales Pct_growth
  2019-04-01 100 1.25
  2019-04-01 80 1.33
  </code></pre>
  
  <p>or a year-over-year comparison </p>
  
  <pre><code>Date Sales YOY_growth
  2019-04-01 100 1.43
  2019-03-01 80 1.33
  </code></pre>
  
  <p>Thanks in advance.</p>