Employees Reports to CEO | Advanced SQL Interview Questions | Data Engineer Interview Question | SQL

Video 383: This is the 47th video of the SQL Interview Question series.

00:00 - Introduction to dataset and Question
04:00 - Approach 1: Using Subqueries
11:30 - Approach 2: Using Recursive CTE
15:00 - Conclusion

We are given Employees table which has the Employee_id, their name and the manager_id they are reporting to.
We are asked to find all the employees who directly or indirectly report to CEO, who is the head of the company

In this video, I covers two approaches, highlighting both the logic and the SQL implementation. Here's what you'll learn:

*** Approach 1: Using Subqueries ***
This approach breaks the problem into manageable levels of the hierarchy. It uses multiple Common Table Expressions (CTEs) to identify:
1. Employees who directly report to the head of the company.
2. Employees who report to these direct reports (second level).
3. Employees who report to the second level, forming the third level of the hierarchy.
Finally, the results are combined using UNION ALL to gather employees from all levels. This method is structured and ideal for organizations with a fixed depth of hierarchy.

*** Approach 2: Using Recursive CTE ***
This elegant and scalable approach leverages SQL's recursive capabilities to dynamically traverse the organizational hierarchy. Starting with employees who directly report to the head of the company, the recursive query iteratively finds all employees reporting to anyone in the hierarchy.

This method is efficient and can handle deeper hierarchies without the need to define levels explicitly.

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