00:00:00

Description

Editorial

Student-Subject Exam Attendance Matrix

EASY15 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

students =

[{"student_id":1,"student_name":"Alice"},{"student_id":2,"student_name":"Bob"},{"student_id":13,"student_name":"John"},{"student_id":6,"student_name":"Alex"}]

subjects =

[{"subject_name":"Math"},{"subject_name":"Physics"},{"subject_name":"Programming"}]

examinations =

[{"student_id":1,"subject_name":"Math"},{"student_id":1,"subject_name":"Physics"},{"student_id":1,"subject_name":"Programming"},{"student_id":2,"subject_name":"Programming"},{"student_id":1,"subject_name":"Physics"},{"student_id":1,"subject_name":"Math"},{"student_id":13,"subject_name":"Math"},{"student_id":13,"subject_name":"Programming"},{"student_id":13,"subject_name":"Physics"},{"student_id":2,"subject_name":"Math"},{"student_id":1,"subject_name":"Math"}]

Loading problem...

101

00:00:00

Description

Editorial

Student-Subject Exam Attendance Matrix

EASY15 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

students =

[{"student_id":1,"student_name":"Alice"},{"student_id":2,"student_name":"Bob"},{"student_id":13,"student_name":"John"},{"student_id":6,"student_name":"Alex"}]

subjects =

[{"subject_name":"Math"},{"subject_name":"Physics"},{"subject_name":"Programming"}]

examinations =

Student-Subject Exam Attendance Matrix

Hints

Student-Subject Exam Attendance Matrix

Hints