Domain 1EasyCORE

Students and Examinations

The problem involves two tables: `Students` and `Examinations`. The `Students` table contains student IDs and their names, while the `Examinations` table records which students took which exams. We need to find out how many times each student has taken each exam.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 0

1280. Students and Examinations

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 0
Acceptance rate: 75.3%


Problem Breakdown

The problem involves two tables: Students and Examinations. The Students table contains student IDs and their names, while the Examinations table records which students took which exams. We need to find out how many times each student has taken each exam.

Inputs:

  • Students: A table with columns student_id (integer) and student_name (string).
  • Examinations: A table with columns student_id (integer) and exam_id (integer).

Outputs:

  • A result set that includes the student name, exam ID, and the count of how many times each student has taken each exam. The result should be ordered by student_name in ascending order.

Implicit Constraints or Tricky Edge Cases:

  • Duplicates in the Examinations table (a student taking an exam multiple times).
  • Students who have not taken any exams.
  • Exams that no students have taken.

Intuition

The brute-force approach would involve iterating over each student and counting their occurrences for each exam. This would be inefficient, especially if the number of students or exams is large, as it would require nested loops.

A better approach involves using SQL’s GROUP BY clause to aggregate the data efficiently. By grouping by both student_id and exam_id, we can count the occurrences directly in the database, which is much faster than processing the data in application code.

Approach

  1. Join Tables: First, join the Students table with the Examinations table on student_id to get a combined dataset that includes student names and their exam IDs.
  2. Group By: Group the result by both student_name and exam_id.
  3. Count Occurrences: Use the COUNT(*) function to count how many times each student has taken each exam.
  4. Order Results: Order the final result set by student_name in ascending order.

Complexity Analysis

  • Time Complexity: The time complexity is O(n log n) due to the sorting operation, where n is the number of unique (student, exam) pairs.
  • Space Complexity: The space complexity is O(n) for storing the intermediate and final result sets.

Solution

import pandas as pd

def students_and_examinations(students: pd.DataFrame, examinations: pd.DataFrame) -> pd.DataFrame:
    # Join the Students and Examinations tables on student_id
    joined_df = students.merge(examinations, on='student_id', how='left')
    
    # Group by student_name and exam_id, then count occurrences
    grouped_df = joined_df.groupby(['student_name', 'exam_id']).size().reset_index(name='count')
    
    # Sort the result by student_name in ascending order
    sorted_df = grouped_df.sort_values(by=['student_name'], ascending=True)
    
    return sorted_df[['student_name', 'exam_id', 'count']]

Edge Cases Handled

  • Students with no exams: The LEFT JOIN ensures that students who have not taken any exams are included in the result set with a count of 0.
  • Exams with no students: These will naturally be excluded from the result set since they do not appear in the joined table.
  • Duplicates in Examinations: The GROUP BY clause and COUNT(*) function handle duplicates by counting them as separate occurrences.