title: SQL Query for 7 Day Rolling Average in SQL Server
body: <p>I have a table of hourly product usage (how many times the product is used) data – </p>
  
  <pre><code>ID (bigint)| ProductId (tinyint)| Date (int - YYYYMMDD) | Hour (tinyint)| UsageCount (int)
  #|1 | 20140901 | 0 | 10
  #|1 | 20140901 | 1 | 15
  #|1 | 20140902 | 5 | 25
  #|1 | 20140903 | 5 | 25
  #|1 | 20140904 | 3 | 25
  #|1 | 20140905 | 7 | 25
  #|1 | 20140906 | 10 | 25
  #|1 | 20140907 | 9 | 25
  #|1 | 20140908 | 5 | 25
  #|2 | 20140903 | 16 | 10
  #|2 | 20140903 | 13 | 115
  </code></pre>
  
  <p>Likewise, I have the usage data for 4 different products (ProductId from 1 through 4) stored for every hour in the product_usage table. As you can imagine, it is constantly growing as the nightly ETL process dumps the data for the entire previous day. If a product is not used on any hour of a day, the record for that hour won’t appear in this table. Similarly, if a product is not used for the entire day, there won’t be any record for that day in the table. I need to generate a report that gives daily usage and last 7 days’ rolling average –</p>
  
  <p>For example:</p>
  
  <pre><code>ProductId | Date | DailyUsage | RollingAverage
  1 | 20140901 | sum of usages of that day | (Sum of usages from 20140901 through 20140826) / 7
  1 | 20140901 | sum of usages of that day | (Sum of usages from 20140901 through 20140826) / 7
  1 | 20140902 | sum of usages of that day | (Sum of usages from 20140902 through 20140827) / 7
  2 | 20140902 | sum of usages of that day | (Sum of usages from 20140902 through 20140827) / 7
  </code></pre>
  
  <p>And so on..
  I am planning to create an Indexed View in SQL server 2014. Can you think of an efficient SQL query to do this?</p>