Interview Question Bank · Relational division · HAVING with FILTER · NULLs in bool_and
9. Students who passed every course they took
HardEducationQuestion #225
Concept: Relational division · HAVING with FILTER · NULLs in bool_andshow
'Every' questions are relational division: group each person's rows and keep the group only if the rows meeting the condition are all of them, as in HAVING COUNT(*) FILTER (WHERE ok) = COUNT(*). Watch NULLs: bool_and and NOT EXISTS (... failed ...) both treat an unknown outcome as harmless, so rows with no result yet slip through. A person with no rows at all passes NOT EXISTS vacuously, so start from the rows, not the people.
Example (different data):
SELECT supplier_id, COUNT(*) AS shipments FROM shipments GROUP BY supplier_id HAVING COUNT(*) FILTER (WHERE arrived_on <= promised_on) = COUNT(*);
Your task
Brightpath Academy offers an 'all clear' scholarship to students who passed every course they enrolled in. A course is passed with a final_score of 60 or more; an enrollment still in progress or withdrawn (no final_score) doesn't count as passed.
For every student with at least one enrollment where every enrollment has final_score >= 60, return student (name) and courses_passed (their number of enrollments). Sort by student.
Starting Postgres in your browser…
Checking your access…