Courses Where More Than a Quarter of Students Failed

Medium
133.

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. 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

ColumnType
roll_noint
course_codevarchar
marksint

Primary key (together): roll_no, course_code.

One row per registered student per course; marks is out of 100, or NULL when the student was absent.

Example

The rows must come back in the order shown.

EndSemResult

roll_nocourse_codemarks
2101CS30135
2102CS30162
2103CS30138
2104CS30171
2101MA20139
2102MA20140
2103MA20155
2104MA20181
2101EC21022
2102EC210NULL
1 more row

Output

course_codeappearedfailedfail_pct
CS3014250
EC2102150
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.

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

ColumnType
roll_noint
course_codevarchar
marksint

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_nocourse_codemarks
2101CS30135
2102CS30162
2103CS30138
2104CS30171
2101MA20139
2102MA20140
2103MA20155
2104MA20181
2101EC21022
2102EC210NULL
2103EC21064

Output

course_codeappearedfailedfail_pct
CS3014250
EC2102150

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_code

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

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