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. code: https://github.com/jeganpillai/adv_sql_50_questions/blob/main/adv50_49_subtask_that_did_not_execute.sql Follow me on, Website : https://growwithdata.co/ YouTube : https://www.youtube.com/@growwithdata TikTok : https://www.tiktok.com/@growwithdata LinkedIn : https://www.linkedin.com/company/growwithdata/ Facebook : https://www.facebook.com/growwithdata.co/ FB Group : facebook.com/groups/datainterviewpreparation twitter : https://twitter.com/growwithdata_co Instagram : https://www.instagram.com/growwithdata.co/ WhatsApp : https://whatsapp.com/channel/0029VaF8pkb77qVNfbp5pA0S TheWide : https://thewide.com/profile/891 #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