Find the Quiet Students in All Exams - Practice Coding Problems

Find the Quiet Students in All Exams - Problem

Database Hard

Find the Quiet Students in All Exams

Imagine you're analyzing student performance in a university database. You have access to two tables: one containing student information and another tracking exam results across different subjects.

The Challenge: Identify the "quiet students" - those who consistently perform at a moderate level without being the star performer or the lowest scorer in any exam they take.

Definition of a Quiet Student:
• Must have taken at least one exam
• In every exam they took, they never scored the highest score
• In every exam they took, they never scored the lowest score

Tables:
Student table contains student IDs and names
Exam table contains exam results with (exam_id, student_id, score)

Goal: Return all quiet students ordered by student_id, showing both their ID and name.

This problem tests your ability to work with complex SQL queries involving window functions, subqueries, and conditional filtering across multiple records.

Input & Output

example_1.sql — Basic Case

$ Input: Student table:\n| student_id | student_name |\n|------------|--------------|\n| 1 | Daniel |\n| 2 | Jade |\n| 3 | Stella |\n| 4 | Jonathan |\n| 5 | Will |\n\nExam table:\n| exam_id | student_id | score |\n|---------|------------|-------|\n| 10 | 1 | 70 |\n| 10 | 2 | 80 |\n| 10 | 3 | 90 |\n| 20 | 1 | 80 |\n| 20 | 2 | 70 |\n| 20 | 3 | 60 |\n| 30 | 1 | 70 |\n| 30 | 4 | 80 |

› Output: | student_id | student_name |\n|------------|--------------|\n| 1 | Daniel |

💡 Note: Daniel (student 1) is the only quiet student. In exam 10: scored 70 (neither highest 90 nor lowest 70). In exam 20: scored 80 (neither highest 80 nor lowest 60). In exam 30: scored 70 (neither highest 80 nor lowest 70). Jade was highest in exam 10 and lowest in exam 20, so not quiet. Stella was highest in exam 10 and lowest in exam 20, so not quiet.

example_2.sql — No Quiet Students

$ Input: Student table:\n| student_id | student_name |\n|------------|--------------|\n| 1 | Alice |\n| 2 | Bob |\n\nExam table:\n| exam_id | student_id | score |\n|---------|------------|-------|\n| 1 | 1 | 90 |\n| 1 | 2 | 80 |\n| 2 | 1 | 70 |\n| 2 | 2 | 90 |

› Output: | student_id | student_name |\n|------------|--------------|\n(empty result)

💡 Note: No quiet students exist. Alice scored highest (90) in exam 1 and lowest (70) in exam 2. Bob scored lowest (80) in exam 1 and highest (90) in exam 2. Both students achieved extreme scores in at least one exam.

example_3.sql — Single Exam Edge Case

$ Input: Student table:\n| student_id | student_name |\n|------------|--------------|\n| 1 | Charlie |\n| 2 | Diana |\n| 3 | Eve |\n\nExam table:\n| exam_id | student_id | score |\n|---------|------------|-------|\n| 1 | 1 | 85 |\n| 1 | 2 | 90 |\n| 1 | 3 | 80 |

› Output: | student_id | student_name |\n|------------|--------------|\n| 1 | Charlie |

💡 Note: Charlie is quiet because in the single exam, he scored 85 which is neither the highest (90) nor the lowest (80). Diana scored highest and Eve scored lowest, so they're not quiet.

Constraints

1 ≤ student_id ≤ 1000
1 ≤ exam_id ≤ 1000
0 ≤ score ≤ 100
Each student takes at least 0 exams (students who never took exams should not be returned)
Multiple students can have the same score in an exam
A quiet student must have taken at least one exam

Visualization

Tap to expand

Understanding the Visualization

Examine Each Exam

For each exam, we identify the highest scorer, lowest scorer, and everyone in between

Track Student Performance

We track each student across all exams they participated in

Identify Quiet Students

Students who never achieved extreme scores (highest or lowest) in any exam are quiet

Return Results

Join with Student table to get names and return ordered by student_id

Key Takeaway

🎯 Key Insight: Window functions provide an elegant solution by ranking students within each exam partition, allowing us to efficiently identify those who never achieved extreme rankings across all their exams.

Asked in

G Google 45 a Amazon 38 f Meta 29 ⊞ Microsoft 22

The optimal solution uses window functions to efficiently rank students within each exam. By applying ROW_NUMBER() with both ascending and descending order, we can identify students who were never ranked 1st (highest or lowest) in any exam. This approach has O(n log n) time complexity and is highly optimized by modern SQL engines.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Nested Subqueries)	O(n²m)	O(1)	Use multiple nested subqueries to check each student's performance
Window Functions (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() and RANK() window functions to efficiently identify quiet students

Brute Force (Nested Subqueries) — Algorithm Steps

Get all students who took at least one exam
For each student, check every exam they took
Verify their score was not the highest in that exam
Verify their score was not the lowest in that exam
Return students who pass both conditions for all their exams

Visualization

Tap to expand

Step-by-Step Walkthrough

Find All Students Who Took Exams

Get the base set of students who participated in at least one exam

Identify Highest Scorers

For each exam, find students who achieved the maximum score

Identify Lowest Scorers

For each exam, find students who achieved the minimum score

Filter Quiet Students

Exclude students who were ever highest or lowest scorers

Code -

solution.c — C

// Complete working C solution using SQL string
#include <stdio.h>
#include <string.h>

char* findQuietStudents() {
    static char query[] = 
        "SELECT DISTINCT s.student_id, s.student_name "
        "FROM Student s "
        "JOIN Exam e ON s.student_id = e.student_id "
        "WHERE s.student_id NOT IN ("
        "    SELECT DISTINCT e1.student_id "
        "    FROM Exam e1 "
        "    WHERE e1.score = ("
        "        SELECT MAX(e2.score) "
        "        FROM Exam e2 "
        "        WHERE e2.exam_id = e1.exam_id"
        "    )"
        ") "
        "AND s.student_id NOT IN ("
        "    SELECT DISTINCT e3.student_id "
        "    FROM Exam e3 "
        "    WHERE e3.score = ("
        "        SELECT MIN(e4.score) "
        "        FROM Exam e4 "
        "        WHERE e4.exam_id = e3.exam_id"
        "    )"
        ") "
        "ORDER BY s.student_id;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²m)

Where n is number of exam records and m is number of exams, as we check each student against all other students in each exam

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond the result set

✓ Linear Space

47.2K Views

Medium-High Frequency

~25 min Avg. Time

1.5K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Nested Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler