Find the Subtasks That Did Not Execute - Practice Coding Problems

Find the Subtasks That Did Not Execute - Problem

Database Hard

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

💡 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

Understanding the Visualization

Input Tables

Tasks with subtask counts and Executed records

Generate All Possible

Cross join to create complete subtask list

Find Missing

LEFT JOIN to identify unexecuted subtasks

Key Takeaway

🎯 Key Insight: Use CROSS JOIN to generate all expected relationships, then LEFT JOIN to find gaps

Asked in

A Amazon 23 M Microsoft 18 G Google 15

Use recursive CTE to generate number series, CROSS JOIN with Tasks to create all possible subtasks, then LEFT JOIN with Executed to find missing subtasks where executed.subtask_id IS NULL.

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)

Common Approaches

Approach	Time	Space	Notes
✓ Cross Join with Number Series	O(n * m)	O(n * m)	Generate all possible subtasks using recursive CTE, then find missing ones with LEFT JOIN

Cross Join with Number Series — Algorithm Steps

Step 1: Create recursive CTE to generate numbers 1 to 20
Step 2: Cross join Tasks with numbers to get all possible subtasks
Step 3: LEFT JOIN with Executed table to find missing subtasks

Visualization

Tap to expand

Step-by-Step Walkthrough

Generate Numbers

Create series 1,2,3...

Cross Join

Tasks × Numbers = All subtasks

Find Missing

LEFT JOIN to identify gaps

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n * m)

Where n is number of tasks and m is average subtask count

✓ Linear Growth

Space Complexity

O(n * m)

Temporary storage for all possible subtasks

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Cross Join with Number Series — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler