Top Three Scores in Each National Mock Test

Hard
145.

Top Three Scores in Each National Mock Test

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

ColumnType
test_idint
test_namevarchar

Primary key: test_id.

Test names are unique.

Table: Learner

ColumnType
learner_idint
learner_namevarchar

Primary key: learner_id.

Learner names are unique.

Table: TestAttempt

ColumnType
attempt_idint
test_idint
learner_idint
scoreint

Primary key: attempt_id.

One row per sitting; score is out of 300 and never NULL.

Example

The rows must come back in the order shown.

MockTest

test_idtest_name
1JEE Main Mock 4
2CAT Mock 2

Learner

learner_idlearner_name
501Aarav
502Diya
503Ishaan
504Kavya
505Neha
506Rohan

TestAttempt

attempt_idtest_idlearner_idscore
11501212
21501248
31502248
41503230
51504199
61505230
71506185
82502141
92503156
102503120

Output

test_namepositionlearner_namebest_score
CAT Mock 21Ishaan156
CAT Mock 22Diya141
JEE Main Mock 41Aarav248
JEE Main Mock 41Diya248
JEE Main Mock 42Ishaan230
JEE Main Mock 42Neha230
JEE Main Mock 43Kavya199
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
test_idint
test_namevarchar

Primary key: test_id.

Test names are unique.

Table: Learner

ColumnType
learner_idint
learner_namevarchar

Primary key: learner_id.

Learner names are unique.

Table: TestAttempt

ColumnType
attempt_idint
test_idint
learner_idint
scoreint

Primary key: attempt_id.

One row per sitting; score is out of 300 and never NULL.

Examples

Example 1

MockTest

test_idtest_name
1JEE Main Mock 4
2CAT Mock 2

Learner

learner_idlearner_name
501Aarav
502Diya
503Ishaan
504Kavya
505Neha
506Rohan

TestAttempt

attempt_idtest_idlearner_idscore
11501212
21501248
31502248
41503230
51504199
61505230
71506185
82502141
92503156
102503120

Output

test_namepositionlearner_namebest_score
CAT Mock 21Ishaan156
CAT Mock 22Diya141
JEE Main Mock 41Aarav248
JEE Main Mock 41Diya248
JEE Main Mock 42Ishaan230
JEE Main Mock 42Neha230
JEE Main Mock 43Kavya199

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_name

Another 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 →