CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
zealous
zealous

Reputation: 7503

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?

Upvotes: 2

Views: 638

Answers (1)

GMB
GMB

Reputation: 222582

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

Upvotes: 2

Related Questions