Backlogs Cleared on a Later Attempt — SQL Subqueries Problem
The exam cell tracks supplementary exams. A student who scored below 40 on their first attempt at a subject has a backlog; they may reappear (attempts 2, 3,…
- Difficulty: Medium
- Topics: Subqueries, Conditional Logic
- Dialect: MySQL
- Problem: #140
Problem statement
The exam cell tracks supplementary exams. A student who scored below 40 on their first attempt at a subject has a backlog; they may reappear (attempts 2, 3, …) until they score 40 or more. Students also sometimes reappear after passing to improve their marks.
Return roll_no, subject_code and cleared_on_attempt — the number of the first attempt with 40 or more — for every student-subject pair that failed attempt 1 and has since passed. Pairs that passed at the first attempt, or have not passed yet, are left out. Order the rows by roll_no, then by subject_code.
Tables
Table: ExamAttempt
| Column | Type |
|---|---|
| roll_no | int |
| subject_code | varchar |
| attempt_no | int |
| marks | int |
Primary key: roll_no, subject_code, attempt_no.
Attempts of a pair are numbered 1, 2, 3, … with no gaps; marks is out of 100 and never NULL.
Examples
Example 1
ExamAttempt
| roll_no | subject_code | attempt_no | marks |
|---|---|---|---|
| 2101 | MA101 | 1 | 32 |
| 2101 | MA101 | 2 | 38 |
| 2101 | MA101 | 3 | 51 |
| 2101 | PH102 | 1 | 64 |
| 2101 | PH102 | 2 | 78 |
| 2102 | MA101 | 1 | 28 |
| 2102 | MA101 | 2 | 40 |
| 2102 | MA101 | 3 | 66 |
| 2103 | CS103 | 1 | 22 |
| 2103 | CS103 | 2 | 35 |
Output
| roll_no | subject_code | cleared_on_attempt |
|---|---|---|
| 2101 | MA101 | 3 |
| 2102 | MA101 | 2 |
How to solve Backlogs Cleared on a Later Attempt
Think of each student-subject pair as a small group of attempts. Two conditions decide whether it is in the answer: attempt 1 failed (marks below 40), and some attempt passed. The value to report is the smallest attempt number among the passing ones.
The reference keeps only the passing attempts, groups them by pair and takes MIN(attempt_no); a correlated EXISTS keeps the pair only if its attempt 1 is a failure. Pairs that passed straight away fail the EXISTS, and pairs still failing have no passing rows to group. Improvement attempts after a pass are larger numbers and never affect the MIN.
The same result comes from one grouped pass with conditional aggregates: MIN(CASE WHEN marks >= 40 THEN attempt_no END) is the clearing attempt (NULL when none) and MAX(CASE WHEN attempt_no = 1 AND marks < 40 THEN 1 ELSE 0 END) flags a failed first attempt. Or start from the failed first attempts and look the clearing attempt up with a scalar subquery. Exactly 40 is a pass in every form.
Reference solution (MySQL)
SELECT e.roll_no, e.subject_code, MIN(e.attempt_no) AS cleared_on_attempt
FROM ExamAttempt e
WHERE e.marks >= 40
AND EXISTS (
SELECT 1 FROM ExamAttempt f
WHERE f.roll_no = e.roll_no AND f.subject_code = e.subject_code
AND f.attempt_no = 1 AND f.marks < 40
)
GROUP BY e.roll_no, e.subject_code
ORDER BY e.roll_no, e.subject_codeAnother way
SELECT roll_no, subject_code, cleared_on_attempt FROM (SELECT roll_no, subject_code, MIN(CASE WHEN marks >= 40 THEN attempt_no END) AS cleared_on_attempt, MAX(CASE WHEN attempt_no = 1 AND marks < 40 THEN 1 ELSE 0 END) AS failed_first FROM ExamAttempt GROUP BY roll_no, subject_code) t WHERE failed_first = 1 AND cleared_on_attempt IS NOT NULL ORDER BY roll_no, subject_codeAnother way
SELECT f.roll_no, f.subject_code, (SELECT MIN(p.attempt_no) FROM ExamAttempt p WHERE p.roll_no = f.roll_no AND p.subject_code = f.subject_code AND p.marks >= 40) AS cleared_on_attempt FROM ExamAttempt f WHERE f.attempt_no = 1 AND f.marks < 40 AND EXISTS (SELECT 1 FROM ExamAttempt p WHERE p.roll_no = f.roll_no AND p.subject_code = f.subject_code AND p.marks >= 40) ORDER BY 1, 2← Course Catalogue Summary by Department and Level · Longest Daily Learning Streak of Each Learner →