Top Three Scores in Each National Mock Test — SQL Problem
A test-prep platform runs national mock tests and publishes a leaderboard of the top three distinct scores of each test.
- Difficulty: Hard
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #145
Problem statement
A test-prep platform runs national mock tests and publishes a leaderboard of the top three distinct scores of each test. Learners may retake a test; only a learner's best score on that test counts. Everyone who shares a top-three score is listed — two learners tied on the best score are both position 1, and the next distinct score is position 2.
Return test_name, position (1, 2 or 3), learner_name and best_score. Order the rows by test_name, then by position, then by learner_name.
Tables
Table: MockTest
| Column | Type |
|---|---|
| test_id | int |
| test_name | varchar |
Primary key: test_id.
Test names are unique.
Table: Learner
| Column | Type |
|---|---|
| learner_id | int |
| learner_name | varchar |
Primary key: learner_id.
Learner names are unique.
Table: TestAttempt
| Column | Type |
|---|---|
| attempt_id | int |
| test_id | int |
| learner_id | int |
| score | int |
Primary key: attempt_id.
One row per sitting; score is out of 300 and never NULL.
Examples
Example 1
MockTest
| test_id | test_name |
|---|---|
| 1 | JEE Main Mock 4 |
| 2 | CAT Mock 2 |
Learner
| learner_id | learner_name |
|---|---|
| 501 | Aarav |
| 502 | Diya |
| 503 | Ishaan |
| 504 | Kavya |
| 505 | Neha |
| 506 | Rohan |
TestAttempt
| attempt_id | test_id | learner_id | score |
|---|---|---|---|
| 1 | 1 | 501 | 212 |
| 2 | 1 | 501 | 248 |
| 3 | 1 | 502 | 248 |
| 4 | 1 | 503 | 230 |
| 5 | 1 | 504 | 199 |
| 6 | 1 | 505 | 230 |
| 7 | 1 | 506 | 185 |
| 8 | 2 | 502 | 141 |
| 9 | 2 | 503 | 156 |
| 10 | 2 | 503 | 120 |
Output
| test_name | position | learner_name | best_score |
|---|---|---|---|
| CAT Mock 2 | 1 | Ishaan | 156 |
| CAT Mock 2 | 2 | Diya | 141 |
| JEE Main Mock 4 | 1 | Aarav | 248 |
| JEE Main Mock 4 | 1 | Diya | 248 |
| JEE Main Mock 4 | 2 | Ishaan | 230 |
| JEE Main Mock 4 | 2 | Neha | 230 |
| JEE Main Mock 4 | 3 | Kavya | 199 |
How to solve Top Three Scores in Each National Mock Test
Two steps matter here, in this order. First, a learner's retakes must not take more than one place on the board, so reduce TestAttempt to one best score per learner per test with MAX(score) grouped by test and learner. Ranking raw attempts instead would list Aarav twice if both his sittings were in the top three.
Second, rank those best scores within each test. The statement's positions — ties share a position and the next distinct score takes the next number — are DENSE_RANK() ordered by score descending. RANK() would skip a number after a tie (1, 1, 3), and ROW_NUMBER() would split tied learners. Keep position <= 3, which may well return more than three learners when there are ties, and join the test and learner tables for the names.
Without windows, a learner's position is one plus the number of distinct best scores above theirs in the same test, computed with a correlated COUNT(DISTINCT …); it is quadratic per test but gives the same rows.
Reference solution (MySQL)
WITH best AS (
SELECT test_id, learner_id, MAX(score) AS best_score
FROM TestAttempt
GROUP BY test_id, learner_id
), ranked AS (
SELECT test_id, learner_id, best_score,
DENSE_RANK() OVER (PARTITION BY test_id ORDER BY best_score DESC) AS position
FROM best
)
SELECT t.test_name, r.position, l.learner_name, r.best_score
FROM ranked r
JOIN MockTest t ON t.test_id = r.test_id
JOIN Learner l ON l.learner_id = r.learner_id
WHERE r.position <= 3
ORDER BY t.test_name, r.position, l.learner_nameAnother way
WITH best AS (SELECT test_id, learner_id, MAX(score) AS best_score FROM TestAttempt GROUP BY test_id, learner_id) SELECT t.test_name, (SELECT COUNT(DISTINCT o.best_score) FROM best o WHERE o.test_id = b.test_id AND o.best_score > b.best_score) + 1 AS position, l.learner_name, b.best_score FROM best b JOIN MockTest t ON t.test_id = b.test_id JOIN Learner l ON l.learner_id = b.learner_id WHERE (SELECT COUNT(DISTINCT o.best_score) FROM best o WHERE o.test_id = b.test_id AND o.best_score > b.best_score) < 3 ORDER BY t.test_name, position, l.learner_name← Course Funnel From Enrolment to Certificate · Clashing Exam Slots in Students' Timetables →