523. Rank Students in Class

HardWindow FunctionsDENSE_RANK

For students with marks, return class, name, total score as total and their dense rank within the class by total as class_rank. Order by class, then class_rank, then name.

students

id	name	class	city	dob
1	Arun	10A	Chennai	2010-05-14
2	Bala	10A	Madurai	2010-08-02
3	Charu	10B	Chennai	2009-12-25
4	Deepa	10B	Coimbatore	2010-03-30
5	Ezhil	10A	Chennai	2010-11-11
6	Farah	10C	NULL	2010-01-19

subjects

id	title	max_marks
1	Maths	100
2	Science	100
3	English	100
4	Tamil	100

marks

student_id	subject_id	score
1	1	95
1	2	88
1	3	76
2	1	67
2	2	72
2	3	81
3	1	99
3	2	94
3	3	90
4	1	45
4	2	58
4	3	62
5	1	88
5	2	91
5	3	85
1	4	90
3	4	85

Your query runs on a fresh copy of these tables (SQLite). Column names in your result do not need to match.

Loading the MyLoginn IDE…

student_id	subject_id	score
1	1	95
1	2	88
1	3	76
2	1	67
2	2	72
2	3	81
3	1	99
3	2	94
3	3	90
4	1	45
4	2	58
4	3	62
5	1	88
5	2	91
5	3	85
1	4	90
3	4	85

student_id	subject_id	score
1	1	95
1	2	88
1	3	76
2	1	67
2	2	72
2	3	81
3	1	99
3	2	94
3	3	90
4	1	45
4	2	58
4	3	62
5	1	88
5	2	91
5	3	85
1	4	90
3	4	85

student_id	subject_id	score
1	1	95
1	2	88
1	3	76
2	1	67
2	2	72
2	3	81
3	1	99
3	2	94
3	3	90
4	1	45
4	2	58
4	3	62
5	1	88
5	2	91
5	3	85
1	4	90
3	4	85