Count Student Number in Departments - Practice Coding Problems

Count Student Number in Departments - Problem

Database Medium

🎓 Count Student Number in Departments

You're working as a database administrator for a university that needs to generate a comprehensive department report. The university maintains two tables: one tracking students and their department assignments, and another containing all department information.

Your task is to create a report showing every department along with the number of students currently enrolled in each one. This includes departments that might have zero students - the administration wants a complete picture of all departments, regardless of enrollment status.

Key Requirements:

Show all departments from the Department table
Count students in each department (including 0 for empty departments)
Sort by student count (highest first), then by department name alphabetically for ties

The result should help administrators identify popular programs and departments that might need more marketing or resources.

Input & Output

Basic Example

$ Input: Student: | student_id | student_name | gender | dept_id | |------------|--------------|--------|---------| | 1 | Jack | M | 1 | | 2 | Jane | F | 1 | | 3 | Mark | M | 2 | Department: | dept_id | dept_name | |---------|-------------| | 1 | Engineering | | 2 | Science | | 3 | Law |

› Output: | dept_name | student_number | |-------------|----------------| | Engineering | 2 | | Science | 1 | | Law | 0 |

💡 Note: Engineering has 2 students (Jack and Jane), Science has 1 student (Mark), and Law has 0 students. Results are ordered by student count descending.

Tied Departments

$ Input: Student: | student_id | student_name | gender | dept_id | |------------|--------------|--------|---------| | 1 | Alice | F | 1 | | 2 | Bob | M | 2 | Department: | dept_id | dept_name | |---------|----------| | 1 | Math | | 2 | Art | | 3 | Biology |

› Output: | dept_name | student_number | |-----------|----------------| | Art | 1 | | Math | 1 | | Biology | 0 |

💡 Note: Math and Art both have 1 student, so they're ordered alphabetically (Art before Math). Biology has 0 students and comes last.

All Empty Departments

$ Input: Student: | student_id | student_name | gender | dept_id | |------------|--------------|--------|---------| (empty table) Department: | dept_id | dept_name | |---------|----------| | 1 | Physics | | 2 | Chemistry |

› Output: | dept_name | student_number | |-----------|----------------| | Chemistry | 0 | | Physics | 0 |

💡 Note: No students exist, so all departments have 0 students. With equal counts, departments are sorted alphabetically.

Visualization

Tap to expand

Understanding the Visualization

Gather Department List

Start with complete list of all university departments

Match Students

For each department, find all students belonging to it

Count & Record

Count students per department (0 for empty departments)

Sort Report

Order by student count (highest first), then alphabetically

Key Takeaway

🎯 Key Insight: LEFT JOIN ensures all departments appear in the result (preserving departments with 0 students), while GROUP BY and COUNT efficiently aggregate student numbers in a single database operation.

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Single pass through departments (n) and students (m) with efficient JOIN operation

✓ Linear Growth

Space Complexity

O(1)

No additional space needed beyond result set and join processing

✓ Linear Space

Constraints

1 ≤ Department table rows ≤ 100
0 ≤ Student table rows ≤ 1000
All department IDs in Student table exist in Department table
Must include departments with 0 students
Department names are unique and non-null

Asked in

A Amazon 45 ⊞ Microsoft 38 O Oracle 32 G Google 28

The optimal solution uses a LEFT JOIN between Department and Student tables, combined with GROUP BY to count students per department. The LEFT JOIN ensures all departments appear in results (even with 0 students), while COUNT efficiently aggregates student numbers in a single pass through the data.

Common Approaches

Approach	Time	Space	Notes
✓ LEFT JOIN (Optimal)	O(n + m)	O(1)	Use LEFT JOIN to combine tables and GROUP BY to count students efficiently
Brute Force (Multiple Subqueries)	O(n*m)	O(1)	Count students for each department using separate subqueries

LEFT JOIN (Optimal) — Algorithm Steps

LEFT JOIN Department with Student tables to preserve all departments
GROUP BY department to aggregate student counts
Use COUNT to count students per department
ORDER BY student count descending, then department name ascending

Visualization

Tap to expand

Step-by-Step Walkthrough

LEFT JOIN

Join departments with students, keeping all departments

GROUP BY

Group results by department

COUNT

Count students in each group

ORDER BY

Sort by count desc, then name asc

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Single pass through departments (n) and students (m) with efficient JOIN operation

✓ Linear Growth

Space Complexity

O(1)

No additional space needed beyond result set and join processing

✓ Linear Space

Constraints

1 ≤ Department table rows ≤ 100
0 ≤ Student table rows ≤ 1000
All department IDs in Student table exist in Department table
Must include departments with 0 students
Department names are unique and non-null

24.4K Views

High Frequency

~12 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

🎓 Count Student Number in Departments

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

LEFT JOIN (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler