medium30 ptsRanking Null Handling Group By

Class Rank by Total Score

exam_results holds one row per student per subject. A NULL score counts as 0. A student's total_score is the sum of their scores.

Rank students within their class by total_score (highest first) using dense ranking: tied students share a rank and the next rank is not skipped.

Return columns class_id, student_id, total_score, class_rank. Order by class_id ascending, class_rank ascending, then student_id ascending.

Schema

CREATE TABLE exam_results (
  student_id INTEGER NOT NULL,
  class_id TEXT NOT NULL,
  subject TEXT NOT NULL,
  score INTEGER
);

Sample data

exam_results

student_id	class_id	subject	score
1	8A	Math	80
1	8A	Science	70
2	8A	Math	95
2	8A	Science	60
3	8A	Math	50
3	8A	Science	NULL
4	8B	Math	88
4	8B	Science	91
5	8B	Math	77
5	8B	Science	85

Hint 1

SUM over only NULLs is NULL, not 0. COALESCE each score first.

Hint 2

DENSE_RANK() OVER (PARTITION BY class_id ORDER BY total_score DESC).

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.