தமிழில்-Employees Reports to CEO | Advanced SQL Interview Question| Data Engineer Interview Question

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

00:00 - Introduction to dataset and Question
03:50 - Approach 1: Using Subqueries
09:00 - Approach 2: Using Recursive CTE
11:15 - 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.

code: https://github.com/jeganpillai/adv_sql_50_questions/blob/main/adv50_47_employees_reports_to_ceo.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

Видео தமிழில்-Employees Reports to CEO | Advanced SQL Interview Question| Data Engineer Interview Question канала Grow with Data