Count Buggy and Bug-Free Submissions per Employee
Problem statement
Return each employee’s name, number of buggy code submissions and number of bug-free code submissions. Sort by employee name ascending, then employee ID ascending to break ties.
For this exercise, every employee must appear, including those with no submissions; their two counts are zero. Join employees.id to code_submissions.employee_id. A true has_bug flag means buggy and false means bug-free. Flags are non-null, and every submission row counts once regardless of timestamp.
Group by employee identity, not name or department. Two employees with the same name remain two rows. Return columns employee_name, buggy_count and bug_free_count, in that order. Compare the simple ASCII names in case-sensitive ASCII order.
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.
employees
One row per employee; names and departments may repeat.
| Column | Type | Nullable | Description |
|---|---|---|---|
| idPK | Integer | No | Employee identifier. |
| name | Text | No | Employee name. |
| department | Text | No | Department, not an aggregation key. |
code_submissions
Every row counts as one code submission.
| Column | Type | Nullable | Description |
|---|---|---|---|
| submission_idPK | Integer | No | Unique submission identifier. |
| employee_id | Integer | No | Employee owning this submission. |
| submitted_at | Timestamp | No | Whole-second UTC submission time; not a filter. |
| has_bug | Boolean | No | True is buggy; false is bug-free. No null flags. |
Foreign key: employee_id → employees(id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| employee_name | Text | No | Employee name; duplicate names retain distinct employee rows. |
| buggy_count | Integer | No | Number of this employee’s submissions with true has_bug. |
| bug_free_count | Integer | No | Number of this employee’s submissions with false has_bug. |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- All columns are non-null and each table follows its declared primary and foreign keys.
- has_bug is a boolean; no unknown or null flag occurs.
- Names and departments may repeat. Practice names use simple ASCII letters.
- submitted_at is a whole-second UTC timestamp and does not restrict which submissions count.