Most Experienced Employees | Advanced SQL Interview Questions | Data Engineer Interview Question
Video 375: This is the 43rd video of the SQL Interview Question series.
00:00 - Introduction to dataset and Question
04:45 - Approach 1: Using CTE Logic
09:45 - Approach 2: Using Subquery Logic
12:45 - Approach 3: Using Correlated Subquery
19:30 - Approach 4: Using Window Functions
24:00 - Conclusion
We are given two tables, Project and Employee. We are asked to find the experienced employee in each project. If there are more than one employee with same experience, then show all those employees
In this video, I walk you through solving the problem of identifying the most experienced employees assigned to each project. We explore four different SQL approaches to tackle this challenge. Let’s dive into each method:
** Approach 1: Using CTE Logic **
We start with a Common Table Expression (CTE) to calculate the maximum experience for each project. By joining the CTE back with the original tables, we identify the employees with the most experience for each project.
** Approach 2: Using Subquery Logic **
This method leverages a subquery to find the maximum experience years for each project. We then join the subquery with the employee and project tables to get the required results.
** Approach 3: Using Correlated Subquery **
Here, we use a correlated subquery that calculates the maximum experience for each project directly within the main query. This approach avoids the need for joins while delivering the same results.
** Approach 4: Using Window Functions **
The final approach utilizes window functions to rank employees within each project based on their experience. We filter the results to retrieve only the employees with the maximum experience for each project.
For a comprehensive understanding of these SQL methodologies and their application, please refer to this explanatory video.
code: https://github.com/jeganpillai/adv_sq...
Follow me on,
Website : https://growwithdata.co/
YouTube : / @growwithdata
TikTok : / growwithdata
LinkedIn : / growwithdata
Facebook : / growwithdata.co
FB Group : facebook.com/groups/datainterviewpreparation
twitter : / growwithdata_co
Instagram : / growwithdata.co
WhatsApp : https://whatsapp.com/channel/0029VaF8...
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