Problem Statement:
I have an EXAM
table which has following fields and sample data
student_id exam_date exam_score ----------------------------------- a1 2018-03-29 75 a1 2018-04-25 89 b2 2018-02-24 91
I want to write a SQL query to output the following fields
1. student_id 2. exam_date 3. highest_score_to_date 4. average_score_to_date 5. highest_exam_score_ever
My SQL Query:
select a.student_id, b.exam_date, highest_exam_score_to_date, average_exam_score_to_date, highest_exam_score_ever from ( select student_id, exam_date, max(score) as highest_exam_score_to_date, avg(score) as average_exam_score_to_date from exam group by student_id, exam_date ) a left join ( select student_id, max(score) as highest_exam_score_ever from exam group by student_id ) b on a.student_id = b.student_id
Can I achieve my result by writing a better SQL query?
Advertisement
Answer
You could indeed join the table with several aggregate subqueries that compute the relevant values at student and exam level.
But it seems simpler to use window functions:
select t.*, max(exam) over(partition by student_id order by exam_date) highest_score_to_date, avg(exam) over(partition by student_id order by exam_date) average_score_to_date, max(exam) over(partition by student_id) highest_score_ever from mytable t