SQL- Finding the users with the Maximum average score

Question

Here is my schema:

create TABLE sample (
  userId      INT,
  score       INT
);

INSERT INTO sample VALUES (1,10);
INSERT INTO sample VALUES (1,15);
INSERT INTO sample VALUES (1,20);

INSERT INTO sample VALUES (2,100);
INSERT INTO sample VALUES (2,200);
INSERT INTO sample VALUES (2,500);

INSERT INTO sample VALUES (4,100);
INSERT INTO sample VALUES (4,200);
INSERT INTO sample VALUES (4,500);

INSERT INTO sample VALUES (3,5);
INSERT INTO sample VALUES (3,5);
INSERT INTO sample VALUES (3,10);
INSERT INTO sample VALUES (3,7);
INSERT INTO sample VALUES (3,2);

I want to find the user ID's of those who have the maximum highest average score. Note there could be more than one! So for the above sample data, the answer would be: 2 and 4, becuase they both have a average score of 266.666... .

I have a working SQL for this problem:

select s.USERID
from sample s
group by USERID
having AVG(s.SCORE) IN (
  -- Gets the Maximum Average Score (returns only 1 result)
  select MAX(average_score) as Max_Average_Score
  from (
    -- Gets the Average Score
    select AVG(s2.SCORE) as average_score
    from sample s2
    group by USERID
  )
);

But I think it is a bit inefficient because I'm calculating the average score twice. Once for the main SQL and again for finding the max avg score. Is there a better way?

Note: I'm using SQL Plus

see this: http://stackoverflow.com/questions/10557897/sql-getting-a-maxavgcolumn — Piyush Parashar, Nov 19 '14 at 14:55
Does each userId have the same number of records? If so, then you can just use SUM, rather than AVG, which might compute quicker... — mlinth, Nov 19 '14 at 14:56

score 4 · Answer 1 · answered Nov 19 '14 at 14:59

select userid from
(select userid, rank() over (order by avg(score) desc) rw
from sample group by userid)
where rw = 1;

Calculate avg score for each user, than calculate the rank of each score using analytic functions (which are performed after the grouping). Lastly get the rows with the first rank

score 2 · Accepted Answer · edited May 23 '17 at 12:27

2

I can only say how I would solve this with DB2 SQL I would create a temponary table where you can save the average score for each user and select the maximum value from it. This is possible in sql plus too: How to create a temporary table in Oracle.

Here is the solution in DB2 Syntax (not tested)

http://www.cs.newpaltz.edu/~pletcha/DB/db2_TempTables.html

WITH tempTable 
AS (select userid, AVG(score) FROM sample GROUP BY userid)

SELECT * FROM tempTable WHERE score = (SELECT MAX(score) FROM tempTable)

edited May 23 '17 at 12:27

Community

1
1

answered Nov 19 '14 at 14:59

stupidstudent

678
4
13

1

That's not a "temp table", it's a "common table expression" (but would work for Oracle just as well) – Nov 19 '14 at 15:07
I found this link helpful in regards to temp tables: http://www.dba-oracle.com/t_sql_rewrite_temporary_tables.htm in Oracle – Yahya Uddin Nov 19 '14 at 15:57

score 0 · Answer 3 · edited Nov 19 '14 at 15:34

0

Listo, changed to Oracle:

select userId, AVG(score) AS promedio 
from [sample] 
where rownum = 1
group by userId
order by AVG(score) desc

edited Nov 19 '14 at 15:34

Eduardo Vélez

193
1
4

answered Nov 19 '14 at 15:04

Eduardo Vélez

1

1

This is not Oracle syntax. – Gordon Linoff Nov 19 '14 at 15:06
1

and some of it is not in english – Yahya Uddin Nov 19 '14 at 15:13
`[sample]` is an invalid SQL identifier. – Nov 19 '14 at 15:35
Also I want ALL users with the highest average score, NOT just 1. Also i'm not sure what [sample] means. is this the same as sample. – Yahya Uddin Nov 19 '14 at 15:54

SQL- Finding the users with the Maximum average score

3 Answers3