Query for Exam Score calculation

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?

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