Department Enrollment Totals

Medium

A university analytics platform stores enrollment data in two tables: Student(student_id, student_name, gender, dept_id) Department(dept_id, dept_name) Return a summary row for every department showing how many students are currently enrolled. Output rules: Include departments with no students (count should be 0). Return exactly these columns: dept_name, student_number. Sort by student_number descending, then dept_name ascending. Supported submission formats: SQL (SQLite 3.27.2) Python (Pandas)

Example 1

Input

Student:
| student_id | student_name | gender | dept_id |
|------------|--------------|--------|---------|
| 1          | Nora         | F      | 10      |
| 2          | Ivan         | M      | 10      |
| 3          | Leena        | F      | 11      |

Department:
| dept_id | dept_name   |
|---------|-------------|
| 10      | Engineering |
| 11      | History     |
| 12      | Law         |

Output

[{"dept_name":"Engineering","student_number":2},{"dept_name":"History","student_number":1},{"dept_name":"Law","student_number":0}]

Explanation

Law has no students but still appears with count 0 due to left-join semantics.

Example 2

Input

Student:
| student_id | student_name | gender | dept_id |
|------------|--------------|--------|---------|
| 7          | Omar         | M      | 2       |
| 8          | Pia          | F      | 1       |
| 9          | Ruth         | F      | 2       |
| 10         | Sam          | M      | 1       |

Department:
| dept_id | dept_name  |
|---------|------------|
| 1       | Analytics  |
| 2       | Design     |
| 3       | Finance    |

Output

[{"dept_name":"Analytics","student_number":2},{"dept_name":"Design","student_number":2},{"dept_name":"Finance","student_number":0}]

Explanation

Analytics and Design tie at 2, so alphabetical order breaks the tie.

Example 3

Input

Student:
(empty)

Department:
| dept_id | dept_name    |
|---------|--------------|
| 20      | Bioinformatics |
| 21      | Materials      |

Output

[{"dept_name":"Bioinformatics","student_number":0},{"dept_name":"Materials","student_number":0}]

Explanation

When Student has no rows, every department count is 0.

Constraints

1 <= number of rows in Department <= 50,000
0 <= number of rows in Student <= 500,000
Department.dept_id is unique
Student.student_id is unique
Every Student.dept_id references an existing Department.dept_id
dept_name is non-empty text
Use SQLite 3.27.2 behavior for SQL submissions
For Pandas submissions, return a DataFrame with columns: dept_name, student_number

Preparing your editor. You can read the problem while it loads.

Department Enrollment Totals

Medium

A university analytics platform stores enrollment data in two tables: Student(student_id, student_name, gender, dept_id) Department(dept_id, dept_name) Return a summary row for every department showing how many students are currently enrolled. Output rules: Include departments with no students (count should be 0). Return exactly these columns: dept_name, student_number. Sort by student_number descending, then dept_name ascending. Supported submission formats: SQL (SQLite 3.27.2) Python (Pandas)

Example 1

Input

Student:
| student_id | student_name | gender | dept_id |
|------------|--------------|--------|---------|
| 1          | Nora         | F      | 10      |
| 2          | Ivan         | M      | 10      |
| 3          | Leena        | F      | 11      |

Department:
| dept_id | dept_name   |
|---------|-------------|
| 10      | Engineering |
| 11      | History     |
| 12      | Law         |

Output

[{"dept_name":"Engineering","student_number":2},{"dept_name":"History","student_number":1},{"dept_name":"Law","student_number":0}]

Explanation

Law has no students but still appears with count 0 due to left-join semantics.

Example 2

Input

Student:
| student_id | student_name | gender | dept_id |
|------------|--------------|--------|---------|
| 7          | Omar         | M      | 2       |
| 8          | Pia          | F      | 1       |
| 9          | Ruth         | F      | 2       |
| 10         | Sam          | M      | 1       |

Department:
| dept_id | dept_name  |
|---------|------------|
| 1       | Analytics  |
| 2       | Design     |
| 3       | Finance    |

Output

[{"dept_name":"Analytics","student_number":2},{"dept_name":"Design","student_number":2},{"dept_name":"Finance","student_number":0}]

Explanation

Analytics and Design tie at 2, so alphabetical order breaks the tie.

Example 3

Input

Student:
(empty)

Department:
| dept_id | dept_name    |
|---------|--------------|
| 20      | Bioinformatics |
| 21      | Materials      |

Output

[{"dept_name":"Bioinformatics","student_number":0},{"dept_name":"Materials","student_number":0}]

Explanation

When Student has no rows, every department count is 0.

Constraints

1 <= number of rows in Department <= 50,000
0 <= number of rows in Student <= 500,000
Department.dept_id is unique
Student.student_id is unique
Every Student.dept_id references an existing Department.dept_id
dept_name is non-empty text
Use SQLite 3.27.2 behavior for SQL submissions
For Pandas submissions, return a DataFrame with columns: dept_name, student_number