Leetcode : 1280 (Counting Student Exam Attendances)

Problem Statement:
We need to find out how many times each student attended each exam. The data is spread across three tables: Students, Subjects, and Examinations. The result should include all students and all subjects, even if a student did not attend any exams for a particular subject.

---

Key Steps in the Solution:

1. Combine All Students and Subjects:

   • Use a CROSS JOIN between the Students and Subjects tables to create all possible combinations of students and subjects. This ensures that every student is paired with every subject, even if they didn’t attend any exams.

2. Count Exam Attendances:

   • Use a LEFT JOIN with the Examinations table to count how many times each student attended each exam. If a student did not attend any exams for a subject, the count will be 0.

3. Group and Order the Results:

   • Group the results by student_id, student_name, and subject_name to calculate the count of attended exams.
   • Order the results by student_id and subject_name for a clean and organized output.

---

SQL Query:

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;

---

Example Input Tables:

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

---

Example 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 of the Output:

• 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, Physics, and Programming exams 1 time each.

---

Why This Query Works:

• The CROSS JOIN ensures all student-subject combinations are included.
• The LEFT JOIN ensures that students who did not attend any exams are still included with a count of 0.
• The COUNT function calculates the number of times each student attended each exam.
• The GROUP BY and ORDER BY clauses organize the results for clarity.

---