Count buggy vs non-buggy by employer
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
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.
Overview: This question evaluates proficiency in SQL data manipulation—particularly conditional aggregation and join semantics—for counting categorized records across related tables in a data science context.
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.
Tables
employers(employer_id INT, name VARCHAR(100))
submissions(submission_id INT, employer_id INT, created_at DATE, is_buggy BOOLEAN)
Hints
- Start from employers and LEFT JOIN to submissions so that employers with no submissions are still included.
- Use SUM(CASE WHEN <condition> THEN 1 ELSE 0 END) to compute conditional counts for buggy and non-buggy submissions.