title: Find Average Score to Date
body: <p>I have a student table as below;</p>
  <pre><code>CREATE TABLE exam_scores
  (
   student_id text,
   exam_date date,
   exam_score int
   );
   
  insert into exam_scores
  Values
  ('a001','2018-03-29',75),
  ('a001','2018-04-25',89),
  ('b002','2018-02-24',91);
  </code></pre>
  <p>I am trying to create a view such as;</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>student_id</th>
  <th>exam_date</th>
  <th>max_score_to_date</th>
  <th>avg_score_to_date</th>
  <th>max_score_ever</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>a001</td>
  <td>2018-03-29</td>
  <td>75</td>
  <td>75</td>
  <td>89</td>
  </tr>
  <tr>
  <td>a001</td>
  <td>2018-04-25</td>
  <td>89</td>
  <td>82</td>
  <td>89</td>
  </tr>
  <tr>
  <td>b002</td>
  <td>2018-02-24</td>
  <td>91</td>
  <td>91</td>
  <td>91</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I can calculate all aggregated columns except &quot;avg_score_to_date&quot;. I have my query below. Can you please help me modify my code, so it works correctly to calculate the average score to date?</p>
  <pre><code>SELECT
       student_id
      ,exam_date
      ,MAX(exam_score) OVER(Partition by student_id,exam_date) AS max_score_to_date
      ,AVG(exam_score) OVER(Partition by student_id,exam_date) avg_score_to_date
      ,MAX(exam_score) OVER(Partition by student_id) AS max_score_ever
   FROM exam_scores
  </code></pre>
  <p>Thank you!</p>