Find the Subtasks That Did Not Execute - Problem
Problem Overview:
Imagine you're a project manager tracking task execution in a distributed system. Each major task is broken down into multiple subtasks, and you need to identify which subtasks failed to execute.
You have two tables:
•
•
Your Goal: Find all the missing subtasks for each task. For example, if Task 1 has 3 subtasks (1, 2, 3) but only subtasks 1 and 3 were executed, then subtask 2 is missing.
Key Details:
• Subtasks are numbered from 1 to
• Each task is guaranteed to have between 2 and 20 subtasks
• The result should show
This is a classic "find missing elements" problem that tests your ability to work with ranges and set differences in SQL.
Imagine you're a project manager tracking task execution in a distributed system. Each major task is broken down into multiple subtasks, and you need to identify which subtasks failed to execute.
You have two tables:
•
Tasks table: Contains each task and how many subtasks it was divided into•
Executed table: Contains records of which specific subtasks were successfully executedYour Goal: Find all the missing subtasks for each task. For example, if Task 1 has 3 subtasks (1, 2, 3) but only subtasks 1 and 3 were executed, then subtask 2 is missing.
Key Details:
• Subtasks are numbered from 1 to
subtasks_count for each task• Each task is guaranteed to have between 2 and 20 subtasks
• The result should show
task_id and subtask_id for each missing subtaskThis is a classic "find missing elements" problem that tests your ability to work with ranges and set differences in SQL.
Input & Output
example_1.sql — Basic Missing Subtasks
$
Input:
Tasks:
| task_id | subtasks_count |
| 1 | 3 |
| 2 | 2 |
Executed:
| task_id | subtask_id |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
›
Output:
| task_id | subtask_id |
| 1 | 1 |
| 1 | 3 |
💡 Note:
Task 1 should have subtasks 1, 2, 3 but only subtask 2 was executed, so subtasks 1 and 3 are missing. Task 2 should have subtasks 1, 2 and both were executed, so no missing subtasks for task 2.
example_2.sql — Complete Task Execution
$
Input:
Tasks:
| task_id | subtasks_count |
| 1 | 2 |
Executed:
| task_id | subtask_id |
| 1 | 1 |
| 1 | 2 |
›
Output:
| task_id | subtask_id |
(empty result)
💡 Note:
Task 1 has 2 subtasks and both were executed (subtasks 1 and 2), so there are no missing subtasks.
example_3.sql — No Execution
$
Input:
Tasks:
| task_id | subtasks_count |
| 3 | 4 |
Executed:
| task_id | subtask_id |
(empty table)
›
Output:
| task_id | subtask_id |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
💡 Note:
Task 3 should have 4 subtasks (1, 2, 3, 4) but none were executed, so all subtasks are missing.
Constraints
- 1 ≤ task_id ≤ 100
- 2 ≤ subtasks_count ≤ 20
- 1 ≤ subtask_id ≤ subtasks_count
- subtask_id is guaranteed to be valid for each task_id
- The combination (task_id, subtask_id) in Executed table is unique
Visualization
Tap to expand
Understanding the Visualization
1
Read Task Requirements
Each task specifies how many subtasks (1 to N) should exist
2
Generate Expected Set
Create all possible (task_id, subtask_id) combinations
3
Check Execution Records
See which subtasks were actually executed
4
Find the Gaps
Missing subtasks = Expected - Executed
Key Takeaway
🎯 Key Insight: This problem is fundamentally about set difference - generate the complete expected set, then subtract what actually exists. SQL's NOT EXISTS clause makes this operation efficient and readable.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code