Department Employee Counts and Running Total
Straive● MediumFULLTIMENEW GRADOAProblem statement
For this exercise, assume every declared department must appear in the result, including departments with no employees.
Given Department(dep_id, dep_name) and Employee(emp_id, dep_id, emp_name, salary), compute one row per department with these columns:
dep_idanddep_name: that department's ID and name.employee_count: the number of employee rows assigned to that department.cumulative_count: the sum of employee counts for all departments whose IDs are less than or equal to this department's ID.
Return rows in ascending numeric dep_id order. Department and employee IDs are unique primary keys. Every employee references one declared department. Names may repeat and do not identify employees or departments; salary does not affect either count. With no departments, return an empty result with the same four columns.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
Department
| Column | Type | Nullable | Description |
|---|---|---|---|
| dep_idPK | Integer | No | — |
| dep_name | Text | No | — |
Employee
| Column | Type | Nullable | Description |
|---|---|---|---|
| emp_idPK | Integer | No | — |
| dep_id | Integer | No | — |
| emp_name | Text | No | — |
| salary | Integer | No | — |
Foreign key: dep_id → Department(dep_id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| dep_id | Integer | No | — |
| dep_name | Text | No | — |
| employee_count | Integer | No | — |
| cumulative_count | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- For this practice contract, Department contains 0 to 200 rows and Employee contains 0 to 1500 rows.
- All columns are non-null. IDs are integers between 1 and 1000000000. Names contain 1 to 100 characters. Salary is an integer between 0 and 1000000000.
- Primary keys are unique; Employee.dep_id references Department.dep_id. Input rows may arrive in any order.
- Return exact integer counts; do not remove distinct IDs because their names or salaries match.