SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (
  `student_id` INTEGER,
  `level` TEXT,
  `name` TEXT,
  `outcome` TEXT,
  `grade` TEXT,
  `date` TEXT
);
INSERT INTO tablename
  (`student_id`, `level`, `name`, `outcome`, `grade`, `date`)
VALUES
  ('10001', 'Higher', 'Mathematics', 'Pass', 'A', '2022-04-01'),
  ('10001', 'Higher', 'Mathematics', 'Fail', 'F', '2022-02-01'),
  ('10002', 'Higher', 'English', 'Pass', 'B', '2022-04-01');
SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
  AND EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
          AND t2.date > t1.date 
          AND t2.outcome = 'Pass'
      );


student_id
level
name
outcome
grade
date


10001
Higher
Mathematics
Fail
F
2022-02-01


SELECT * 
FROM tablename
EXCEPT
SELECT t1.*
FROM tablename t1
WHERE t1.outcome = 'Fail'
  AND EXISTS (
        SELECT 1
        FROM tablename t2
        WHERE (t2.student_id, t2.level, t2.name) = (t1.student_id, t1.level, t1.name)
          AND t2.date > t1.date 
          AND t2.outcome = 'Pass'
      );


student_id
level
name
outcome
grade
date


10001
Higher
Mathematics
Pass
A
2022-04-01

10002
Higher
English
Pass
B
2022-04-01

student_id	level	name	outcome	grade	date
10001	Higher	Mathematics	Pass	A	2022-04-01
10002	Higher	English	Pass	B	2022-04-01