Find the Subtasks That Did Not Execute - Problem

You are given two tables: Tasks and Executed.

The Tasks table contains information about tasks and their subtask counts. Each task is divided into subtasks labeled from 1 to subtasks_count.

The Executed table contains records of which subtasks were successfully executed for each task.

Write a SQL query to find all the missing subtasks for each task - that is, the subtasks that were supposed to run but did not execute.

Return the result table in any order.

Table Schema

Tasks
Column Name Type Description
task_id PK int Unique identifier for each task
subtasks_count int Total number of subtasks for this task (2-20)
Primary Key: task_id
Executed
Column Name Type Description
task_id PK int Task identifier (foreign key to Tasks)
subtask_id PK int ID of the executed subtask
Primary Key: (task_id, subtask_id)

Input & Output

Example 1 — Basic Missing Subtasks
Input Tables:
Tasks
task_id subtasks_count
1 3
2 2
3 4
Executed
task_id subtask_id
1 2
3 1
3 3
3 4
Output:
task_id subtask_id
1 1
1 3
2 1
2 2
3 2
💡 Note:

Task 1 has 3 subtasks (1,2,3) but only subtask 2 executed, so subtasks 1 and 3 are missing. Task 2 has 2 subtasks (1,2) but none executed, so both are missing. Task 3 has 4 subtasks and executed 1,3,4, so only subtask 2 is missing (but it's not in our output, indicating all expected subtasks for task 3 were covered).
Example 2 — All Subtasks Executed
Input Tables:
Tasks
task_id subtasks_count
1 2
Executed
task_id subtask_id
1 1
1 2
Output:
task_id subtask_id
💡 Note:

Task 1 has 2 subtasks and both subtasks 1 and 2 were executed, so there are no missing subtasks. The result is empty.

Constraints

  • 1 ≤ task_id ≤ 1000
  • 2 ≤ subtasks_count ≤ 20
  • subtask_id ≤ subtasks_count for each task_id

Visualization

Tap to expand
Find Subtasks That Did Not Execute INPUT Tasks Table task_id subtasks_count 1 3 2 2 Executed Table task_id subtask_id 1 2 1 3 2 2 Task 1: Expected [1,2,3] Task 2: Expected [1,2] 1 2 3 1 2 Green = Executed, Gray = Missing ALGORITHM STEPS 1 Generate Expected Subtasks Use RECURSIVE CTE to create numbers 1 to subtasks_count 2 Cross Join with Tasks Generate all possible (task_id, subtask_id) pairs 3 LEFT JOIN Executed Join expected with actually executed subtasks 4 Filter NULL (Not Executed) WHERE Executed.subtask_id IS NULL WITH RECURSIVE nums AS ( SELECT 1 AS n UNION SELECT n+1 FROM nums WHERE n < max_subtasks ) FINAL RESULT Subtasks Not Executed task_id subtask_id 1 1 2 1 Visual Summary Task 1: 1 OK OK Task 2: 1 OK Red = Not Executed (Missing) Green = Executed Successfully Result: 2 Missing Subtasks Task 1 subtask 1, Task 2 subtask 1 Key Insight: Use a RECURSIVE CTE to generate all expected subtask numbers (1 to subtasks_count) for each task. Then LEFT JOIN with the Executed table and filter where the executed subtask_id IS NULL. This efficiently finds the gap between expected and actual executions without procedural code. TutorialsPoint - Find the Subtasks That Did Not Execute | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
28.5K Views
Medium Frequency
~20 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen