Count buggy vs non-buggy by employer

Count buggy vs non-buggy submissions for each employer_id, including employers with zero submissions. Return employer_id, buggy_count, non_buggy_count, ordered by employer_id. Write a single SQL query using conditional aggregation. Also show how you would adapt it if submission status were a string column instead of boolean. Schema and sample data:

Tables

• employers(employer_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL)
• submissions(submission_id INT PRIMARY KEY, employer_id INT NOT NULL, created_at DATE NOT NULL, is_buggy BOOLEAN NOT NULL)

Sample rows (employers)
employer_id | name
1 | Acme
2 | Globex
3 | Initech

Sample rows (submissions)
submission_id | employer_id | created_at | is_buggy
10 | 1 | 2025-08-20 | TRUE
11 | 1 | 2025-08-21 | FALSE
12 | 1 | 2025-08-22 | TRUE
13 | 2 | 2025-08-23 | FALSE
14 | 2 | 2025-08-24 | FALSE

Expected result
employer_id | buggy_count | non_buggy_count
1 | 2 | 1
2 | 0 | 2
3 | 0 | 0

Sub-questions:

• Provide the LEFT JOIN + GROUP BY solution with SUM(CASE WHEN is_buggy THEN 1 ELSE 0 END) and SUM(CASE WHEN NOT is_buggy THEN 1 ELSE 0 END).
• Show the variant when status is a STRING column status IN ('buggy','non_buggy').
• Explain how your query remains correct if a new employer is added with no submissions.

Count buggy vs non-buggy submissions by employer

You are given two tables: employers and submissions.

Tables:

• employers(employer_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL)
• submissions(submission_id INT PRIMARY KEY, employer_id INT NOT NULL, created_at DATE NOT NULL, is_buggy BOOLEAN NOT NULL)

Task: Write a single SQL query that returns, for each employer_id, the total number of buggy submissions and non-buggy submissions, including employers with zero submissions.

The result should have the columns: employer_id, buggy_count, non_buggy_count and be ordered by employer_id.

Requirements:

• Use a LEFT JOIN from employers to submissions.
• Use conditional aggregation with SUM(CASE WHEN ...) to compute buggy_count and non_buggy_count.
• Then, show how you would adapt the conditional aggregation expressions if the submissions table used a string column status with values IN ('buggy', 'non_buggy') instead of the boolean is_buggy column.
• Explain (in comments or verbally) why your LEFT JOIN + GROUP BY approach correctly returns a row with 0,0 for any employer that has no submissions.

Use the schema and sample data below.

Table employers

column	type	constraints
employer_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL

Sample data:

employer_id	name
1	Acme
2	Globex
3	Initech

Table submissions

column	type	constraints
submission_id	INT	PRIMARY KEY
employer_id	INT	NOT NULL
created_at	DATE	NOT NULL
is_buggy	BOOLEAN	NOT NULL

Sample data:

created_at	employer_id	is_buggy	submission_id
2025-08-20	1	true	10
2025-08-21	1	false	11
2025-08-22	1	true	12
2025-08-23	2	false	13
2025-08-24	2	false	14

Expected output

One row per employer_id, showing the count of buggy and non-buggy submissions, including employers with zero submissions, ordered by employer_id.

buggy_count	employer_id	non_buggy_count
2	1	1
0	2	2
0	3	0

Hints

1. Start from employers and LEFT JOIN to submissions so that employers with no submissions are still included.
2. Use SUM(CASE WHEN <condition> THEN 1 ELSE 0 END) to compute conditional counts for buggy and non-buggy submissions.