Subtask that did not Execute | Advanced SQL Interview Questions | Data Engineer Interview Question

Video 387: This is the 49th video of the SQL Interview Question series.

00:00 - Introduction to dataset and Question
04:00 - Approach 1: Hardcoded Subtask Values
12:00 - Approach 2: Recursive CTE Starting from One
19:30 - Approach 3: Recursive CTE Starting from Subtask Count
22:45 - Approach 4: Using NOT IN Instead of LEFT JOIN
24:45 - Conclusion

We are given two tables, Tasks and Executed

The Task table has rows which indicates that task_id was divided into subtasks_count. Each task will have atleast 2 or more subtask.
The Executed table has the task and the subtask_id which got executed. It is guaranteed that subtask_id less than or equal to subtasks_count for each task_id
We are asked to find the IDs of the missing subtasks for each task_id.

In this video, I demonstrate four different approaches to solving this problem, each showcasing unique techniques in SQL. Below is an overview of the approaches covered:

*** Approach 1: Hardcoded Subtask Values ***
This method uses a hardcoded sequence of numbers (from 1 to 4) to generate possible subtasks for each task. A LEFT JOIN is then used to identify missing subtasks. It's a simple and straightforward solution when the number of subtasks is limited.

*** Approach 2: Recursive CTE Starting from One ***
Here, I leverage a recursive common table expression (CTE) to generate all possible subtasks for each task. The recursion starts from subtask 1 and increments until all subtasks are covered. A LEFT JOIN is used to exclude the executed subtasks, leaving only the missing ones.

*** Approach 3: Recursive CTE Starting from Subtask Count ***
This approach also uses a recursive CTE, but the recursion starts from the highest subtask count and decrements until all subtasks are covered. It’s a reverse iteration method compared to Approach 2 and highlights another way to use recursion effectively in SQL.

*** Approach 4: Using NOT IN Instead of LEFT JOIN ***
In this approach, I replace the LEFT JOIN with a NOT IN clause to filter out executed subtasks. This demonstrates an alternative way to handle exclusion logic and compares its efficiency and readability with other solutions.

For a comprehensive understanding of these SQL methodologies and their application, please refer to this explanatory video.

Follow me on,

#sql #dataengineers #tablejoins #ceil #floor #bucket #meta #google #facebook #apple #paypal #netflix #amazon #deinterview #sqlinterview #interviewquestions #leetcode #faang #maanga #mysql #oracle #dbms #query #sqlserver #mysql #coderpad #aggregates #aggregation #nonaggregation #database #placementpreparation #lead #lag #windowsfunction #nullcheck #coalesce #sqlperformance #ifnull #case #lead #lag #windowsfunction #tamil #tamilpython #tamilinterview #tamilinterviewlatest #tamilinterviewquestions #sqlintamil