title: How to use LAG and OVER in BigQuery to calculate a percentage change in a weekly moving average?
body: <p>I’m doing a project with the <a href="https://console.cloud.google.com/marketplace/details/usafacts-public-data/covid19-us-cases?filter=category%3Acovid19&amp;id=3eaff9c5-fbaf-47bb-a441-89db1e1395ab" rel="nofollow noreferrer">BigQuery USA Facts Covid-19 open dataset</a>. The data looks like this:
  <a href="https://i.stack.imgur.com/WO482.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WO482.png" alt="enter image description here" /></a></p>
  <p>I’m trying to create a query that gives me a % change (up or down) in the 7 day covid case data by county. The end result would be the county, the date and the percentage change of the seven day moving average of covid cases. Ultimately this would allow me to show where cases are relatively stable versus where they are increasing, AKA a hotspot.</p>
  <p>I'm new to working with LAG and OVER. So I'm pretty sure I'm just missing some basic order by or group by within my CTE.</p>
  <p>It’s odd because when I only select out one County (where county_name=”X”), I'm able to get the 7 Day moving average just fine -- it just gives me a nice percentage for each day that tells me whether it is increasing or decreasing. The problem is when I am not selecting just one County, I'm just not able to figure out what I need to do or what I need to change in order to still get that same value. I end up getting values that make no sense. I'm pretty sure this is because I'm just using the window function wrong.</p>
  <p>Here’s my code:</p>
  <pre class="lang-sql prettyprint-override"><code>
  WITH 
  a AS (SELECT long.*,
  deaths-lag(deaths) over (order by date) as deaths_increase,
  confirmed_cases - lag(confirmed_cases) over (order by date) as cases_increase,
  FROM `bigquery-public-data.covid19_usafacts.summary` as long
  where date &gt;= cast('2020-05-03' as date)
  
  
  )
  ,b as (
   SELECT
   a.*,
   AVG(a.deaths_increase) OVER(ORDER BY a.date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_avg_deaths,
   AVG(a.cases_increase) OVER(ORDER BY a.date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_avg_cases
  FROM a
  order by a.county_name
  
  )
  
  select 
  b.county_name, 
  b.county_fips_code,
  b.confirmed_cases,
  b.cases_increase,
  b.deaths,
  b.state,
  b.seven_day_avg_cases,
  b.date,
  
  (b.seven_day_avg_cases - lag(b.seven_day_avg_cases) OVER( ORDER BY b.date)) / b.seven_day_avg_cases * 100 as seven_day_percent_change
  
  from b
  
  where seven_day_avg_cases &gt; 0
  
  
  order by date desc
  </code></pre>