Students Above Their Class Average
exam_scores holds each student's score in the final exam, along with their class_id. A NULL score means the student was absent; absent students never qualify and do not count toward the class average.
Find students whose score is strictly greater than their own class's average score.
Return columns student_id, class_id, score. Row order does not matter.
Schema
CREATE TABLE exam_scores ( student_id INTEGER PRIMARY KEY, class_id TEXT NOT NULL, score INTEGER );
Sample data
exam_scores
| student_id | class_id | score |
|---|---|---|
| 1 | 10A | 72 |
| 2 | 10A | 85 |
| 3 | 10A | 64 |
| 4 | 10A | NULL |
| 5 | 10B | 90 |
| 6 | 10B | 58 |
| 7 | 10B | 77 |
| 8 | 10B | 77 |
Hint 1
A correlated subquery can compute the average for the outer row's class.
Hint 2
NULL > anything is not true, so absent students drop out by themselves.
Good practice for: Data Analyst, Data Scientist, Backend Developer
SQL (SQLite)
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.