Courses Where More Than a Quarter of Students Failed
The academic council reviews any course in which more than 25% of the students who sat the end-semester exam failed it.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #133
Problem statement
The academic council reviews any course in which more than 25% of the students who sat the end-semester exam failed it. A student fails with marks below 40; students absent from the exam (marks NULL) did not sit it and are left out of both counts.
Return course_code, appeared (students who sat), failed and fail_pct (failed as a percentage of appeared, rounded to 2 decimal places) for every course above the line. Exactly 25% is not flagged. Order the rows by fail_pct highest first, then by course_code.
Tables
Table: EndSemResult
| Column | Type |
|---|---|
| roll_no | int |
| course_code | varchar |
| marks | int |
Primary key: roll_no, course_code.
One row per registered student per course; marks is out of 100, or NULL when the student was absent.
Examples
Example 1
EndSemResult
| roll_no | course_code | marks |
|---|---|---|
| 2101 | CS301 | 35 |
| 2102 | CS301 | 62 |
| 2103 | CS301 | 38 |
| 2104 | CS301 | 71 |
| 2101 | MA201 | 39 |
| 2102 | MA201 | 40 |
| 2103 | MA201 | 55 |
| 2104 | MA201 | 81 |
| 2101 | EC210 | 22 |
| 2102 | EC210 | NULL |
| 2103 | EC210 | 64 |
Output
| course_code | appeared | failed | fail_pct |
|---|---|---|---|
| CS301 | 4 | 2 | 50 |
| EC210 | 2 | 1 | 50 |
How to solve Courses Where More Than a Quarter of Students Failed
Group the results by course and compute two counts per group. COUNT(marks) counts only non-NULL marks, so absentees drop out of appeared by themselves (filtering marks IS NOT NULL in WHERE does the same and also removes them from every other aggregate). The failures are a conditional count: SUM(CASE WHEN marks < 40 THEN 1 ELSE 0 END), or COUNT(CASE WHEN marks < 40 THEN 1 END), which counts the non-NULL results of the CASE.
Whether a course is flagged depends on the whole group, so the test goes in HAVING. Writing it as failed * 4 > appeared keeps it in integers: exactly a quarter (1 of 4, 2 of 8) is not flagged, and no rounded percentage can push a course over the line by accident. A course where every student was absent has appeared = 0 and is never flagged.
The displayed fail_pct is rounded only for output. One scan and one group-by.
Reference solution (MySQL)
SELECT course_code,
COUNT(marks) AS appeared,
SUM(CASE WHEN marks < 40 THEN 1 ELSE 0 END) AS failed,
ROUND(100 * SUM(CASE WHEN marks < 40 THEN 1 ELSE 0 END) / COUNT(marks), 2) AS fail_pct
FROM EndSemResult
WHERE marks IS NOT NULL
GROUP BY course_code
HAVING SUM(CASE WHEN marks < 40 THEN 1 ELSE 0 END) * 4 > COUNT(marks)
ORDER BY fail_pct DESC, course_codeAnother way
SELECT course_code, appeared, failed, ROUND(100 * failed / appeared, 2) AS fail_pct FROM (SELECT course_code, COUNT(*) AS appeared, SUM(IF(marks < 40, 1, 0)) AS failed FROM EndSemResult WHERE marks IS NOT NULL GROUP BY course_code) t WHERE failed * 4 > appeared ORDER BY fail_pct DESC, course_code ASCAnother way
SELECT course_code, COUNT(marks) AS appeared, COUNT(CASE WHEN marks < 40 THEN 1 END) AS failed, ROUND(100 * COUNT(CASE WHEN marks < 40 THEN 1 END) / COUNT(marks), 2) AS fail_pct FROM EndSemResult GROUP BY course_code HAVING COUNT(marks) > 0 AND COUNT(CASE WHEN marks < 40 THEN 1 END) * 4 > COUNT(marks) ORDER BY 4 DESC, 1← Monthly Fee Collection by Payment Mode · Students Scoring Above Their Section's Internal Average →