I have a student table as below;

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);

I am trying to create a view such as;

I can calculate all aggregated columns except "avg_score_to_date". I have my query below. Can you please help me modify my code, so it works correctly to calculate the average score to date?

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

Thank you!

CodePudding user response：

you just need to do the group by student and add order by exam_date.

    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 order by exam_date) avg_score_to_date
        ,MAX(exam_score) OVER(Partition by student_id) AS max_score_ever
     FROM exam_scores;