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. It may contain duplicates. Each student from the Students table takes every course from the Subjects 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.
Example 1:
Input: Students table: +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Alice | | 2 | Bob | | 13 | John | | 6 | Alex | +------------+--------------+ 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 | | 13 | Math | | 13 | Programming | | 13 | Physics | | 2 | Math | | 1 | Math | +------------+--------------+ Output: +------------+--------------+--------------+----------------+ | student_id | student_name | subject_name | attended_exams | +------------+--------------+--------------+----------------+ | 1 | Alice | Math | 3 | | 1 | Alice | Physics | 2 | | 1 | Alice | Programming | 1 | | 2 | Bob | Math | 1 | | 2 | Bob | Physics | 0 | | 2 | Bob | Programming | 1 | | 6 | Alex | Math | 0 | | 6 | Alex | Physics | 0 | | 6 | Alex | Programming | 0 | | 13 | John | Math | 1 | | 13 | John | Physics | 1 | | 13 | John | Programming | 1 | +------------+--------------+--------------+----------------+ Explanation: The result table should contain all students and all subjects. Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time. Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam. Alex did not attend any exams. John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.
The key idea in #1280 Students and Examinations is to generate every possible combination of students and subjects, and then determine how many times each student attended each exam. To achieve this, start by creating all student–subject pairs using a CROSS JOIN between the Students and Subjects tables.
Next, match these pairs with the Examinations table using a LEFT JOIN. This ensures that even if a student never attended a particular subject’s exam, the pair still appears in the result. Finally, use COUNT() with GROUP BY on student and subject columns to compute how many exams were attended for each pair.
This pattern—generating combinations and then aggregating with joins—is common in SQL interview problems. The time complexity is primarily influenced by the size of the cross join (students × subjects) and the join with the examinations table.
| Approach | Time Complexity | Space Complexity |
|---|---|---|
| CROSS JOIN + LEFT JOIN with GROUP BY | O(S × U + E) | O(S × U) |
Everyday Data Science
This approach involves joining the Students and Subjects tables to generate all combinations of students and subjects. Then, we use a left join with the Examinations table to count how many times each student attended each exam. Group By is used to aggregate and count the occurrences, ensuring that all combinations are accounted for.
Time Complexity: O(N * M + E), where N is the number of students, M is the number of subjects, and E is the number of examination records.
Space Complexity: O(N * M), for storing the cross join results before joining with Examinations.
1SELECT 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;The solution begins with a cross join between the Students and Subjects tables to list every student-subject combination. Then, a left join is performed with the Examinations table on both student_id and subject_name to account for the attendance. We count these occurrences with COUNT(e.student_id) and group the results by student and subject. Finally, we order the results by student_id and subject_name.
This approach uses nested queries to first produce all combinations of students and subjects. The inner query selects results from the Examinations table and the outer query ensures all zero-attendance cases are captured properly using a union operation with defaults.
Time Complexity: O(N * M + E), where N is the number of students, M is the number of subjects, and E is the number of examination records.
Space Complexity: O(N * M), for storing the resulting dataset.
1SELECT Students.student_id, Students.student_name, Subjects.subject_name, COALESCE(Exams.attended_exams, 0This approach involves using a hash map (also called a dictionary in some languages) to keep track of the elements we've seen so far and their counts or indices. This allows us to efficiently find the solution in linear time.
Time Complexity: O(n).
Space Complexity: O(n).
1#
This approach involves first sorting the array and then using a two-pointer technique to find the two numbers that sum to the target. This can be more space-efficient but requires sorting, which takes O(n log n) time.
Not applicable for this solution in C.
1def Watch expert explanations and walkthroughs
Practice problems asked by these companies to ace your technical interviews.
Explore More ProblemsJot down your thoughts, approach, and key learnings
A CROSS JOIN generates all possible combinations of students and subjects. This is important because the final result must show every student for every subject, even if the student never attended the exam.
While the exact problem may not always appear, similar SQL questions involving joins, aggregation, and generating combinations are common in FAANG-style interviews. Practicing this problem helps strengthen relational database query skills.
The optimal SQL approach is to create all student-subject combinations using a CROSS JOIN between the Students and Subjects tables. Then perform a LEFT JOIN with the Examinations table and count the number of matches using GROUP BY. This ensures every pair appears even if the student never attended that exam.
The problem mainly tests understanding of CROSS JOIN, LEFT JOIN, and aggregation using COUNT with GROUP BY. You also need to ensure that missing attendance records still appear with a count of zero, which is why a LEFT JOIN is essential.
Here, we perform a cross join between Students and Subjects to list all student-subject pairs. A nested query inside the left join counts the occurrences of each student-subject appearing in the Examinations table. COALESCE is used to replace NULL with 0 for cases where a student did not attend a particular subject. Finally, results are ordered by student_id and subject_name.
This solution iterates through the array and uses a hash map (implemented with a simple array for space efficiency in this specific example) to store indices of the numbers. For each number, it checks if the complement exists in the map.
This Python solution works by pairing each element with its index, sorting these pairs, and then using two pointers to find the appropriate pair of numbers.