CODEWITHSUNDEEP
sqloracle-database
Ben Plante
Ben Plante

Reputation: 23

Find group by value associated with maximum of a group function

Suppose I have a couple tables for storing a student's test marks, like so

tests (
    testId INT,
    proctoredDate DATE,
    totalMark INT,
    weight FLOAT,
    CONSTRAINT test_id_primary_key PRIMARY KEY(testId),
    ...
)

student_test_marks (
    studentId INT,
    testId INT,
    awardedMark INT,
    CONSTRAINT student_test_composite_key PRIMARY KEY(studentId, testId),
    ...
)

I'm trying to determine which student in a given month earned the highest cummulative mark. So the process is to sum all the test marks and weights grouped by each student in the specified month and return only the maximum sum. I already have a working query but I don't think it's very efficient.

SELECT studentId
FROM student_test_marks INNER JOIN tests
ON student_test_marks.testId = tests.testId
GROUP BY studentId, EXTRACT(MONTH FROM proctoredDate)
HAVING EXTRACT(MONTH FROM proctoredDate) = 4 AND 
SUM((awardedMark / totalMark) * weight) IN (
    SELECT MAX(SUM((awardedMark / totalMark) * weight))
    FROM student_test_marks INNER JOIN tests
    ON student_test_marks.testId = test.testId
    GROUP BY studentId, EXTRACT(MONTH FROM proctoredDate)
    HAVING EXTRACT(MONTH FROM proctoredDate) = 4
)

It does work but I have a feeling its not optimal since the query joins, groups and filters twice. I considered ordering by the Sum column of the group and then fetching only the first row but that feels clunky.

Is there a better way to solve this?

Upvotes: 1

Views: 27

Answers (2)

Amir Kadyrov
Amir Kadyrov

Reputation: 1288

with tests as (
select 1 testid, date '2019-01-10' proctoredDate, 100 totalmark from dual union all
select 2 testid, date '2019-02-22' proctoredDate, 100 totalmark from dual union all
select 3 testid, date '2019-03-14' proctoredDate, 100 totalmark from dual union all
select 4 testid, date '2019-04-19' proctoredDate, 100 totalmark from dual),
student_test_marks as (
select  1 studentid, 1 testid, 50 awardedmark from dual union all
select  2 studentid, 1 testid, 30 awardedmark from dual union all
select  3 studentid, 1 testid, 70 awardedmark from dual union all
select  4 studentid, 1 testid, 60 awardedmark from dual union all
select  1 studentid, 2 testid, 30 awardedmark from dual union all
select  2 studentid, 2 testid, 40 awardedmark from dual union all
select  3 studentid, 2 testid, 50 awardedmark from dual union all
select  4 studentid, 2 testid, 70 awardedmark from dual union all
select  1 studentid, 3 testid, 70 awardedmark from dual union all
select  2 studentid, 3 testid, 60 awardedmark from dual union all
select  2 studentid, 4 testid, 30 awardedmark from dual union all
select  1 studentid, 4 testid, 40 awardedmark from dual union all
select  2 studentid, 4 testid, 50 awardedmark from dual
)
select 
mn, 
max(studentid)  keep (dense_rank last order by sumawarded) studentid,
max(sumawarded) max_sumawarded
from 
  (select trunc(t.proctoredDate, 'month') mn, s.studentid, sum(awardedmark) sumawarded
   from                    tests t
   inner join student_test_marks s on t.testId = s.testId
   group by trunc(t.proctoredDate, 'month'), s.studentid
  )
group by mn;

MN                   STUDENTID MAX_SUMAWARDED
------------------- ---------- --------------
2019-01-01 00:00:00          3             70
2019-02-01 00:00:00          4             70
2019-03-01 00:00:00          1             70
2019-04-01 00:00:00          2             80

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I think you just want to use analytic functions:

SELECT m.*
FROM (SELECT TRUNC(proctoredDate, 'MON') as yyyymm,
             studentId, SUM((awardedMark / totalMark) * weight) as weighted_mark,
             RANK() OVER (PARTITION BY TRUNC(proctoredDate, 'MON')
                          ORDER BY SUM((awardedMark / totalMark) * weight)
                         ) as seqnum
      FROM student_test_marks stm INNER JOIN
           tests t
           ON stm.testId = t.testId
      GROUP BY TRUNC(proctoredDate, 'MON'), studentId
     ) m
WHERE seqnum = 1;

Upvotes: 1

Related Questions