Students and Examinations

Difficulty: Easy | Acceptance: 61.20% | Paid: No Topics: Database

Table: Students +--------------+-----------+ | Column Name | Type | +--------------+-----------+ | student_id | int | | student_name | varchar | +--------------+-----------+ student_id is the primary key (column with unique values) for this table. Each row of this table contains the ID and the name of one student in the school.

Table: Subjects +--------------+-----------+ | Column Name | Type | +--------------+-----------+ | subject_name | varchar | +--------------+-----------+ subject_name is the primary key (column with unique values) for this table. Each row of this table contains the name of one subject in the school.

Table: Examinations +--------------+-----------+ | Column Name | Type | +--------------+-----------+ | student_id | int | | subject_name | varchar | +--------------+-----------+ There is no primary key (column with unique values) for this table. Each row of this table indicates that a student with ID student_id attended the exam of subject_name.

Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

The result format is in the following example.

• Examples
• Constraints
• Approach 1: Cross Join with Left Join
• Approach 2: CTE with Cross Join
• Approach 3: Pre-aggregation Join

Examples

Example 1:

Input: Students table: +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Alice | | 2 | Bob | +------------+--------------+ Subjects table: +--------------+ | subject_name | +--------------+ | Math | | Physics | | Programming | +--------------+ Examinations table: +------------+--------------+ | student_id | subject_name | +------------+--------------+ | 1 | Math | | 1 | Physics | | 1 | Programming | | 2 | Programming | | 1 | Physics | | 1 | Math | +------------+--------------+ Output: +------------+--------------+--------------+------------------+ | student_id | student_name | subject_name | attended_exams | +------------+--------------+--------------+------------------+ | 1 | Alice | Math | 2 | | 1 | Alice | Physics | 2 | | 1 | Alice | Programming | 1 | | 2 | Bob | Math | 0 | | 2 | Bob | Physics | 0 | | 2 | Bob | Programming | 1 | +------------+--------------+--------------+------------------+ Explanation: The result table should contain all students and all subjects. Alice attended Math 2 times, Physics 2 times, and Programming 1 time. Bob attended Math 0 times, Physics 0 times, and Programming 1 time.

Constraints

Input Data Format:
- The tables Students, Subjects, and Examinations are guaranteed to exist.
- There are no duplicate rows in Students and Subjects tables.
- Examinations table may contain duplicate rows.

Approach 1: Cross Join with Left Join

Intuition To list every student for every subject, we need the Cartesian product of the Students and Subjects tables. We then left join the Examinations table to count the attendance for each pair.

Steps

• Perform a CROSS JOIN between Students and Subjects to generate all possible student-subject pairs.
• LEFT JOIN the Examinations table on both student_id and subject_name.
• Group by student_id, student_name, and subject_name.
• Count the non-null entries from the Examinations table to get the attended_exams count.
• Order the results by student_id and subject_name.

def query():
    return """SELECT s.student_id, s.student_name, sub.subject_name, COUNT(e.student_id) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e ON s.student_id = e.student_id AND sub.subject_name = e.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name"""

Complexity

• Time: O(N * M + K), where N is the number of students, M is the number of subjects, and K is the number of examination records.
• Space: O(N * M) for storing the result set.
• Notes: This is the most straightforward approach for generating a complete matrix of data.

Approach 2: CTE with Cross Join

Intuition Using a Common Table Expression (CTE) improves readability by first defining the list of all student-subject pairs before joining the examination data.

Steps

• Define a CTE (e.g., AllPairs) that selects student_id, student_name, and subject_name from the CROSS JOIN of Students and Subjects.
• Select from the CTE and LEFT JOIN Examinations on matching IDs and names.
• Group by the columns from the CTE and count the examinations.
• Order by student_id and subject_name.

def query():
    return """WITH AllPairs AS (
    SELECT s.student_id, s.student_name, sub.subject_name
    FROM Students s
    CROSS JOIN Subjects sub
)
SELECT ap.student_id, ap.student_name, ap.subject_name, COUNT(e.student_id) AS attended_exams
FROM AllPairs ap
LEFT JOIN Examinations e ON ap.student_id = e.student_id AND ap.subject_name = e.subject_name
GROUP BY ap.student_id, ap.student_name, ap.subject_name
ORDER BY ap.student_id, ap.subject_name"""

Complexity

• Time: O(N * M + K)
• Space: O(N * M)
• Notes: Similar performance to Approach 1, but structurally cleaner for complex queries.

Approach 3: Pre-aggregation Join

Intuition We can optimize by first counting the exams for each student-subject pair in the Examinations table, then joining this aggregated result to the full list of student-subject pairs.

Steps

• Create a CTE (e.g., ExamCounts) that groups Examinations by student_id and subject_name to get the count for each pair.
• CROSS JOIN Students and Subjects to get all pairs.
• LEFT JOIN the ExamCounts CTE on student_id and subject_name.
• Select the columns, using COALESCE to replace NULL counts with 0.
• Order by student_id and subject_name.

def query():
    return """WITH ExamCounts AS (
    SELECT student_id, subject_name, COUNT(*) AS cnt
    FROM Examinations
    GROUP BY student_id, subject_name
)
SELECT s.student_id, s.student_name, sub.subject_name, COALESCE(ec.cnt, 0) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN ExamCounts ec ON s.student_id = ec.student_id AND sub.subject_name = ec.subject_name
ORDER BY s.student_id, sub.subject_name"""

Complexity

• Time: O(K + N * M), where K is the size of Examinations. This can be faster if Examinations is very large but sparse, as we reduce the rows before the final join.
• Space: O(N * M)
• Notes: Efficient for datasets where the Examinations table is significantly larger than the Cartesian product of Students and Subjects.