title: Weekly Average Reports: Redshift
body: <p>My Sales data for first two weeks of june, Monday Date i.e 1st Jun , 8th Jun are below</p>
  
  <p>date                | count</p>
  
  <p>2015-06-01 03:25:53 |   1   </p>
  
  <p>2015-06-01 03:28:51 |   1   </p>
  
  <p>2015-06-01 03:49:16 |   1   </p>
  
  <p>2015-06-01 04:54:14 |   1   </p>
  
  <p>2015-06-01 08:46:15 |   1   </p>
  
  <p>2015-06-01 13:14:09 |   1   </p>
  
  <p>2015-06-01 16:20:13 |   5   </p>
  
  <p>2015-06-01 16:22:13 |   1   </p>
  
  <p>2015-06-01 16:27:07 |   1   </p>
  
  <p>2015-06-01 16:29:57 |   1   </p>
  
  <p>2015-06-01 19:16:45 |   1</p>
  
  <p>2015-06-08 10:54:46 |   1</p>
  
  <p>2015-06-08 15:12:10 |   1   </p>
  
  <p>2015-06-08 20:35:40 |   1   </p>
  
  <p>I need a find weekly avg of sales happened in a given range . </p>
  
  <p>Complex Query:</p>
  
  <pre><code>  (some_manipulation_part), ifact as 
     (  select date, sales_count from final_result_set
  ) select date_part('h',date )) as h  ,  
           date_part('dow',date )) as day_of_week  , 
           count(sales_count) 
    from final_result_set
    group by h, dow.
  </code></pre>
  
  <p>Output : </p>
  
  <p>h | day_of_week |   count   </p>
  
  <p>3  |    1       |   3<br>
  4  |    1       |   1<br>
  8  |    1       |   1<br>
  10 |    1       |   1<br>
  13 |    1       |   1<br>
  15 |    1       |   1<br>
  16 |    1       |   8<br>
  19 |    1       |   1<br>
  20 |    1       |   1   </p>
  
  <p>If I try to apply avg on the above final result, It is not actually fetching correct answer!</p>
  
  <pre><code>(some_manipulation_part), ifact as 
  (  select date, sales_count from final_result_set
  ) select date_part('h',date )) as h  ,  
           date_part('dow',date )) as day_of_week  , 
           avg(sales_count) 
    from final_result_set
    group by h, dow.
  </code></pre>
  
  <p>h | day_of_week |   count   </p>
  
  <p>3  |    1       |   1   </p>
  
  <p>4  |    1       |   1<br>
  8  |    1       |   1<br>
  10 |    1       |   1<br>
  13 |    1       |   1<br>
  15 |    1       |   1<br>
  16 |    1       |   1<br>
  19 |    1       |   1<br>
  20 |    1       |   1   </p>
  
  <p>So I 've two mondays in the given range, it is not actually dividing by it. I am not even sure what is happening inside redshift. </p>