Backlogs Cleared on a Later Attempt

Medium
140.

Backlogs Cleared on a Later Attempt

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

ColumnType
roll_noint
subject_codevarchar
attempt_noint
marksint

Primary key (together): 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.

Example

The rows must come back in the order shown.

ExamAttempt

roll_nosubject_codeattempt_nomarks
2101MA101132
2101MA101238
2101MA101351
2101PH102164
2101PH102278
2102MA101128
2102MA101240
2102MA101366
2103CS103122
2103CS103235

Output

roll_nosubject_codecleared_on_attempt
2101MA1013
2102MA1012
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.

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

ColumnType
roll_noint
subject_codevarchar
attempt_noint
marksint

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_nosubject_codeattempt_nomarks
2101MA101132
2101MA101238
2101MA101351
2101PH102164
2101PH102278
2102MA101128
2102MA101240
2102MA101366
2103CS103122
2103CS103235

Output

roll_nosubject_codecleared_on_attempt
2101MA1013
2102MA1012

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_code

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

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