00:00:00

Description

Editorial

Department Enrollment Totals

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

student =

[{"student_id":1,"student_name":"Nora","gender":"F","dept_id":10},{"student_id":2,"student_name":"Ivan","gender":"M","dept_id":10},{"student_id":3,"student_name":"Leena","gender":"F","dept_id":11}]

department =

[{"dept_id":10,"dept_name":"Engineering"},{"dept_id":11,"dept_name":"History"},{"dept_id":12,"dept_name":"Law"}]

Loading problem...

101

00:00:00

Description

Editorial

Department Enrollment Totals

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

student =

[{"student_id":1,"student_name":"Nora","gender":"F","dept_id":10},{"student_id":2,"student_name":"Ivan","gender":"M","dept_id":10},{"student_id":3,"student_name":"Leena","gender":"F","dept_id":11}]

department =

[{"dept_id":10,"dept_name":"Engineering"},{"dept_id":11,"dept_name":"History"},{"dept_id":12,"dept_name":"Law"}]

Department Enrollment Totals

Hints

Department Enrollment Totals

Hints