Student-Subject Exam Attendance Matrix

Easy

A school analytics system tracks students, subjects, and exam attendance events. Tables: students(student_id, student_name) subjects(subject_name) examinations(student_id, subject_name) Definitions: Each row in examinations means one attendance event for one student in one subject. Duplicate rows in examinations are valid and must be counted as separate attendances. Task: Produce a complete matrix of all student-subject combinations. For each combination, compute how many times that student attended that subject exam. If a student never attended a subject, the count must be 0. Output requirements: Return columns exactly as: student_id, student_name, subject_name, attended_exams Sort by student_id ascending, then subject_name ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

students:
| student_id | student_name |
|------------|--------------|
| 1          | Alice        |
| 2          | Bob          |
| 6          | Alex         |
| 13         | John         |

subjects:
| subject_name |
|--------------|
| Math         |
| Physics      |
| Programming  |

examinations:
| 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":1,"student_name":"Alice","subject_name":"Math","attended_exams":3},
  {"student_id":1,"student_name":"Alice","subject_name":"Physics","attended_exams":2},
  {"student_id":1,"student_name":"Alice","subject_name":"Programming","attended_exams":1},
  {"student_id":2,"student_name":"Bob","subject_name":"Math","attended_exams":1},
  {"student_id":2,"student_name":"Bob","subject_name":"Physics","attended_exams":0},
  {"student_id":2,"student_name":"Bob","subject_name":"Programming","attended_exams":1},
  {"student_id":6,"student_name":"Alex","subject_name":"Math","attended_exams":0},
  {"student_id":6,"student_name":"Alex","subject_name":"Physics","attended_exams":0},
  {"student_id":6,"student_name":"Alex","subject_name":"Programming","attended_exams":0},
  {"student_id":13,"student_name":"John","subject_name":"Math","attended_exams":1},
  {"student_id":13,"student_name":"John","subject_name":"Physics","attended_exams":1},
  {"student_id":13,"student_name":"John","subject_name":"Programming","attended_exams":1}
]

Explanation

The output includes all student-subject pairs. Missing attendance combinations are represented with 0.

Example 2

Input

students:
| student_id | student_name |
|------------|--------------|
| 10         | Mia          |
| 11         | Noah         |

subjects:
| subject_name |
|--------------|
| Algebra      |
| Chemistry    |

examinations:
[]

Output

[
  {"student_id":10,"student_name":"Mia","subject_name":"Algebra","attended_exams":0},
  {"student_id":10,"student_name":"Mia","subject_name":"Chemistry","attended_exams":0},
  {"student_id":11,"student_name":"Noah","subject_name":"Algebra","attended_exams":0},
  {"student_id":11,"student_name":"Noah","subject_name":"Chemistry","attended_exams":0}
]

Explanation

No attendance events exist, so every matrix cell has value 0.

Example 3

Input

students:
| student_id | student_name |
|------------|--------------|
| 21         | Priya        |

subjects:
| subject_name |
|--------------|
| Databases    |
| Networks     |

examinations:
| student_id | subject_name |
|------------|--------------|
| 21         | Databases    |
| 21         | Databases    |
| 21         | Databases    |
| 21         | Networks     |

Output

[
  {"student_id":21,"student_name":"Priya","subject_name":"Databases","attended_exams":3},
  {"student_id":21,"student_name":"Priya","subject_name":"Networks","attended_exams":1}
]

Explanation

Duplicate examination rows count as multiple attendances for the same student-subject pair.

Constraints

0 <= number of rows in students <= 1000
0 <= number of rows in subjects <= 200
0 <= number of rows in examinations <= 200000
1 <= student_id <= 1000000000
student_name and subject_name are non-empty strings
students.student_id is unique
subjects.subject_name is unique
examinations rows reference valid student_id and subject_name values
Duplicate rows may appear in examinations and must be counted
Return every student-subject combination with zero-filled missing counts
Sort by student_id ASC, subject_name ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns in required order

students: | student_id | student_name | |------------|--------------| | 1 | Alice | | 2 | Bob | | 6 | Alex | | 13 | John | subjects: | subject_name | |--------------| | Math | | Physics | | Programming | examinations: | 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 |

[ {"student_id":1,"student_name":"Alice","subject_name":"Math","attended_exams":3}, {"student_id":1,"student_name":"Alice","subject_name":"Physics","attended_exams":2}, {"student_id":1,"student_name":"Alice","subject_name":"Programming","attended_exams":1}, {"student_id":2,"student_name":"Bob","subject_name":"Math","attended_exams":1}, {"student_id":2,"student_name":"Bob","subject_name":"Physics","attended_exams":0}, {"student_id":2,"student_name":"Bob","subject_name":"Programming","attended_exams":1}, {"student_id":6,"student_name":"Alex","subject_name":"Math","attended_exams":0}, {"student_id":6,"student_name":"Alex","subject_name":"Physics","attended_exams":0}, {"student_id":6,"student_name":"Alex","subject_name":"Programming","attended_exams":0}, {"student_id":13,"student_name":"John","subject_name":"Math","attended_exams":1}, {"student_id":13,"student_name":"John","subject_name":"Physics","attended_exams":1}, {"student_id":13,"student_name":"John","subject_name":"Programming","attended_exams":1} ]

students: | student_id | student_name | |------------|--------------| | 10 | Mia | | 11 | Noah | subjects: | subject_name | |--------------| | Algebra | | Chemistry | examinations: []

[ {"student_id":10,"student_name":"Mia","subject_name":"Algebra","attended_exams":0}, {"student_id":10,"student_name":"Mia","subject_name":"Chemistry","attended_exams":0}, {"student_id":11,"student_name":"Noah","subject_name":"Algebra","attended_exams":0}, {"student_id":11,"student_name":"Noah","subject_name":"Chemistry","attended_exams":0} ]

students: | student_id | student_name | |------------|--------------| | 21 | Priya | subjects: | subject_name | |--------------| | Databases | | Networks | examinations: | student_id | subject_name | |------------|--------------| | 21 | Databases | | 21 | Databases | | 21 | Databases | | 21 | Networks |